Quick Links
Microsoft Excel has hundreds of tools that you’re able to use to make your spreadsheets tick. However, if you’re looking to level up your expertise, get to know these six essential features today.
This is a whistle-stop tour of some must-know tools in Microsoft Excel. To find out more about each feature, click the “Related” links in each section.
6Excel Tables for Data Organization
A Microsoft Excel worksheet is essentially a large table organized into columns and rows, with each column-row intersection forming a cell where you can enter data or formulas. Within that larger structure, however, you can create mini datasets that work independently.
This is where Excel tables come into play. They come with built-in filtering and sorting capabilities, expand with new data, duplicate formulas down a whole column, and allow you to quickly add a total row for instant data analysis. What’s more, you can use table column headers in formulas—also known asstructured references—as well as choose from various table formatting options.
If you’re starting with a blank canvas, click “Table” on the Insert tab on the ribbon.
Then, click “OK” in the Create Table dialog box.
Within the newly created table, rename the first column, and start inputting your data.
As soon as you type in the first blank cell beneath the table and press Enter, the table automatically grabs that cell and includes it as part of the dataset. Similarly, adding values to the first blank column next to the table expands the formatting to the right.
If you already have your dataset and want to convert it into a formatted table, select any cell within the range, and in the Home tab on the ribbon, click “Format As Table,” and choose a style.
Next, if your table has headers, check this option in the Create Table dialog box. If it doesn’t, leave this option unchecked, and Excel will add a header row for you.
When you click “OK,” your data is instantly transformed into a tidily presented table.
If your dataset contains formulas that reference other columns within the range, you’ll need to recreate these using structured references once you’ve formatted the data as an Excel table. In other words, if a formula reads =SUM(A2+B2), delete A2 and B2, and replace these direct references with column references by clicking the relevant cells.
Whether you’re starting from scratch or converting a pre-existing dataset into a formatted table, an important step to take is naming your table. This means that it can be referenced in formulas outside the table, and you can jump to it at any point via the name box in the top-left corner of the Excel window.
To do this, select any cell within the table, and in the Table Design tab, head to the “Properties” group, and type a new name.
The Table Design tab is where you can change how your table looks and behaves. You can adjust the overall layout of the table in the Table Styles group, and you can choose whether to include elements like a total row or banding in the Table Style Options group. What’s more, you can add slicers to your table to make it easier to filter your data.
If you’ve never used Excel tables before, you’ll be amazed at just how much easier they make data management and organization.
5Lookup Functions for Cross-Referencing
I probably use lookup functions in my Excel spreadsheets more than any other type of function. They’re a powerful and efficient way to look up a value in a dataset, and return a corresponding result.
Microsoft lists36 lookup and reference functions, but XLOOKUP and INDEX with MATCH are the most useful for day-to-day data referencing. Let me show you some examples.
Example 1: Returning a Value in a One-Dimensional Lookup
In this straightforward example, the aim is to use these functions to return the total score of the player whose name is typed in cell F2.
To do this in cell G2 using the XLOOKUP function, you need to type:
where
When you press Enter, the lookup correctly returns a total score of 9.
To get the same result using the INDEX and MATCH functions, in cell G3, you need to type:
Notice how, when comparing these examples, the XLOOKUP formula is much more straightforward. XLOOKUP was introduced to Excel later than INDEX and MATCH, so it’s a more refined way to complete this one-dimensional lookup task.
Example 2: Returning a Value in a Two-Dimensional Lookup
XLOOKUP and INDEX with MATCH can also perform two-way lookups. In this example, in cells H6 and H7, you want to use these functions to return a player’s score on a particular day, depending on the name in cell F6 (in this case, Mike) and the day in cell G6 (in this case, day 2).
To do this in cell H6 using XLOOKUP, you need to type:
where the first XLOOKUP looks up the value in cell G6 across the column headers, and the second XLOOKUP looks up the value in cell F6 in the Name column. Then, it returns the result from the array.
Using INDEX and MATCH, in cell H7, type:
where the INDEX part of the formula identifies the array containing the value to return, the first MATCH looks up the value in cell F6 in the Name column, and the second MATCH looks up the value in G6 across the column headers.
In this case, the formula when using the INDEX-MATCH-MATCH combination is shorter than the formula when using XLOOKUP-XLOOKUP, so I would tend to use the former for two-dimensional lookups.
Example 3: Using Lookup Functions in a Table
In the examples above, the lookups were performed outside the main data tables. However, you’re able to also use these functions within a formatted Excel table.
In example 3, your aim is to return the students’ grades based on their scores.
When you press Enter, the formula is duplicated automatically to the remaining cells in the column.
To do this using INDEX and MATCH, in cell C14, type:
Then, press Enter to commit the formula and apply it to the other cells in that column.
Both XLOOKUP and INDEX with MATCH require five arguments, and their formulas are of similar lengths. However, since XLOOKUP allows you to specify what to return if the value cannot be found, it’s my go-to in these scenarios.
4Conditional Formatting for Instant Data Visualization
You might know how to use all the complex Excel formulas in the book, but that’s no good if the figures they produce aren’t easy to read. That’s why conditional formatting is an essential tool to use in every spreadsheet. It does what its name suggests—formats cells based on certain conditions.
Imagine you own a grocery store, and you want to clearly visualize how much money you have made on each fruit.
Yes, you can see the figures in column E, but they’re currently not particularly easy to interpret. To fix this, select the data in the column by clicking the black arrow that appears when you hover over the column header, and in the Home tab on the ribbon, click the “Conditional Formatting” drop-down menu, hover over “Color Scales,” and choose the green-to-red option.
Now, you’re able to instantly see which figures are higher, and which figures are lower.
You can also use conditional formatting to set more specific rules. For example, instead of coloring column E using color scales, you want to highlight the whole row of any fruit that has made over $500. To do this, select the highest left-most cell beneath the column headers, and press Ctrl+A to select the whole range. Then, click Conditional Formatting > New Rule.
Now, in the New Formatting Rule dialog box, click “Use A Formula To Determine Which Cells To Format,” and in the text field, type:
This takes all the cells in column E, starting at cell E2, and evaluates their values to see whether they’re greater than 500.
Finally, click “Format” to choose a cell style that will be applied when the above criterion is met. In this case, I’m going for a lilic cell fill.
Click “OK” to close the dialog boxes and complete the process.
Because you selected all the columns in the first step, the whole row for each qualifying value is now highlighted.
Conditional formatting can be used to apply formats in many different contexts, includingformatting certain dates, or applying formatting if a cell’s value is above or below the average.
3Data Validation for Consistency and Error Prevention
Whether you plan to share your spreadsheet with others, or keep it for your own use, data validation is a great tool for ensuring cells contain the values or types of values you expect. As a result, it ensures consistency, helps prevent typos, and saves time.
First, select the cell or cells to which you want to apply the data validation rule, and in the Data tab on the ribbon, click “Data Validation.” If you see a drop-down menu, click “Data Validation” again.
By default, any value can be entered into a cell in Excel. However, if you expand the “Allow” drop-down menu of the Data Validation dialog box, you may see the types of restrictions you can apply. For example, if you select “Whole Number,” “Decimal,” “Date,” “Time,” or “Text Length,” you’re given the option to specify the parameters for those data types.
On the other hand, if you select “List,” you can either type a list of allowed values (separated by commas) in the Source field, or reference cells containing the allowed values.
After you click “OK,” a drop-down button appears when the affected cell is selected, which you can click to reveal the entry options.
Finally, the Data Validation dialog box has two other tabs, where you’re able to further refine the validation cleanliness of the selected cells:
2Power Query for Data Manipulation
Power Query is an underestimated tool that everyone—from Excel novices to power users—can and should use. It allows you to import and connect datasets from various sources, including the web, PDFs, and separate worksheets within a workbook, as well as manipulate the data to meet your needs.
For example, you can split cells into two or more new cells, replace errors with more meaningful values, unpivot data to aid analysis, fill blank cells based on the cell above, and much more. You can alsostack tables that have the same column headers from various worksheets.
To transform existing data in your worksheet, select any cell in the range in question, and in the Data tab on the ribbon, click “From Table/Range.”
The Power Query Editor then opens, where you can begin your data manipulation. The Transform tab is where most of the magic happens, so this is a good place to start. Right-clicking the column headers also gives you access to many column-specific tools.
When you’ve finished editing your data, click “Close And Load” in the Home tab on the ribbon to send the dataset to a new worksheet in your workbook.
To import a table from a website, head back to the “Data” tab on the main Excel ribbon, click “From Web,” and paste the URL into the text field of the window that appears.
When you click “OK,” the Navigator dialog box appears, where you can select the table you want to import and edit, before clicking “Transform Data” to open the Power Query Editor.
1Wildcards for Partial Matches
Wildcards in Excel represent unknown or non-specific characters in searches, filters, and formulas.
There are two types of wildcards you’re able to use to identify values withfuzzymatches:
If you want to search for question marks or asterisks as characters in their own right, place a tilde (~) before either symbol to cancel their wildcard functionality.
Wildcards are most commonly used when searching for certain values in Excel. In this example, after pressing Ctrl+F to launch the Find tab of the Find And Replace dialog box, typing*2??Areturns two results from the dataset: UK255A and CAN267A.
Using the same dataset, you can use wildcards within the SUMIF function to add the prices of goods that come from the UK:
Wildcards and text values must always be enclosed within quotation marks in formulas in Excel.
Finally, after pressing Ctrl+Shift+L to add filter buttons to the dataset, clicking the filter button at the top of column A and typing:
into the text field returns all product codes ending in “A.”
Once you’ve got to grips with these essential tools, test your knowledge by taking on the How-To Geekbeginner,intermediate, andadvancedMicrosoft Excel skills tests.
