Features you should use in Excel but few people know about
Even the simplest spreadsheets — like budgets, lists, tracking sheets, etc. — can be made more efficient with the powerful features in Excel below. But many people often avoid using them because they think they are too complicated. In fact, these features are easier to use than you might think, even helping Excel users save a significant amount of time.
Quick Analysis
Suppose you have a spreadsheet containing sales records. When highlighting some cells, the Quick Analysis tool in Excel will immediately suggest the following actions for you:
- Calculate Total Revenue or Units Sold
- Add charts to visualize Total Profit by Region
- Apply color scales to highlight the most profitable orders
- Convert your data into an Excel Table for easier filtering and sorting
How it works is as follows: When you select a range of cells in Excel, a small icon appears in the lower right corner (it looks like a square with three lines and a lightning bolt). Click this icon, or simply press the key combination Ctrl + QExcel will immediately display a menu of five analysis categories: Formatting, Charts, Totals, Tables, and Sparklines.
Within each category, you’ll see options tailored to your specific data. You can freely customize them as you wish.
Data Validation
Imagine you’re collecting attendance confirmations for your wedding. You need guests to confirm their attendance, choose their food, and be able to indicate whether they are bringing someone along. Without the Data Validation feature, you will get very messy responses: “Yes”, “y”, “Attending”, “Chicken”, “Veggie”, “1”, “one”, or even completely blank cells.
Here’s how to help you avoid the hassle of having to clean up such data:
- Blacken the vehiclet Meal Choice and go to tag Data.
- Find item Data Validation in Data Tools. You can identify this feature by the icon consisting of two rectangles: one with a green check mark and the other with a red error mark.
- Click on it, select List in section Allowthen enter the allowable values, separated by commas.
- Press OK or Apply (depending on the Excel version you are using) and you’re done.
Now, if someone tries to import a dish that is not on the whitelist, they will receive an error message.
Flash Fill
This feature is similar to how Excel “read” your thoughts. Just tell Excel what you want by entering an example or two; Excel will automatically recognize the pattern and do the rest for you.
For example, if you have a column containing full names and want to separate the last names into another column, you don’t need to manually enter them one by one. Just type “Doe” in the box next to “Jessica Doe”, press Enter, then start typing “Lee” in the next box. Excel will understand your intention and offer to auto-complete the entire column for you.
The applications for this feature are endless: you can separate area codes from phone numbers, combine first and last names from separate columns, extract email domains from full email addresses, even normalize messy text formats.
The only secret to mastering the Flash Fill feature is to provide accurate examples. If the first two examples are not enough for Excel to grasp the rule, you just need to add a third example.
Other powerful features to use in Excel
PivotTables
Summarize and cross-analyze large data sets in seconds without writing any formulas — arguably the most powerful yet underused feature in Excel.
Power Query
Automatically import, clean, and reshape data from a variety of sources; The entire process is saved and can be run again at any time — a powerful tool for repetitive data preparation tasks.
Freeze Panes (Fix cells/rows/columns)
Keep row or column headers frozen as you scroll through large spreadsheets — a small feature that makes massive spreadsheets much easier to use.
Goal Seek
Work backwards from a desired outcome to determine the inputs needed to achieve that outcome — very useful when it comes to budgeting and financial planning.
Post Comment