You Can’t Be an Excel Power User Until You Know These 5 Tools

Whether you’re applying for a role that requires Excel expertise, want to add more strings to your data analysis bow, or simply want to do more with your data in Microsoft’s spreadsheet program, add these five Excel skills to your toolkit to unlock the next level.

1

Excel Tables: Organize Your Data

Raw Microsoft Excel data can be in a regular range or a table. A regular range is a collection of cells that are not structurally connected, while a table is an object containing column headers, fields (columns), records (rows), and other features.

When formatted as an Excel table, your figures instantly become more readable, you can easily filter and sort your data, and you can reference specific column headers in formulas to aid accuracy and save time.

To convert your data into a table, select all the cells in the range (including the header row). Alternatively, if your range is contiguous (in other words, all the cells are next to each other with no blank columns or rows), you can select a single cell in the data instead. Then, in the Insert tab on the ribbon, click “Table.” If you prefer using keyboard shortcuts in Microsoft Excel, press Ctrl+T.

Make sure you have column headers along the top row before you convert your range. Taking this step makes dealing with and using the data more straightforward later on.

Next, in the Create Table dialog box, verify that the whole range is selected, check “My Table Has Headers,” and click “OK.”

And there you have it! A nicely formatted table with filter buttons added to your column headers.

By default, tables are named Table1, Table2, Table 3, and so on, but if you rename a table in the Properties group of the Table design tab, this makes your spreadsheet more navigable, helps people using screen readers, and makes formula creation and understanding easier if they reference a column in the table.

Table names must start with a letter, underscore, or backslash, with the other characters being letters, numbers, periods, or underscores. They must also be unique, can’t contain a space, and mustn’t have the same name as a cell reference, like A1.

In the same tab, you can change the table design or choose which elements your table contains.

One of the benefits of formatted Excel tables is that adding columns and rows is really simple. Here, as soon as I typed a column header into cell F1 and a game number into cell A12, Excel expanded the table to capture the extra data.

On the other hand, to insert a column or row in the center of your table, right-click a cell, hover over “Insert,” and choose the appropriate option.

Here’s a summary of the pros and cons of using tables in Excel:

2

Power Query: Import and Shape Your Data

When I learned about Power Query in Excel, it completely changed how I deal with my data. This tool works by connecting to a single or multiple data sources, from which you can extract the necessary information. Then, you can transform and clean your extracted data to meet your needs, and load the result onto a worksheet.

To begin, head to the “Data” tab on the ribbon, and tell Excel where the data source is located, either from one of the options immediately visible or through the Get Data drop-down menu.

Specifically, you can use Power Query to import a single dataset like an Excel workbook, a table from a website, or a database. Alternatively, you can import multiple datasets from worksheets in an Excel file or more than one Excel workbook.

Once you’ve identified the data source, you’re taken to the Power Query Editor, where you’ll do most of your data transformations.

For example, in the Home tab, you can add new sources, manage the parameters, split columns, append queries, and perform a whole host of other common actions. In the Transform tab, you can make changes to existing data, like splitting columns, transposing the data structure, replacing values, extracting text, and much more.

After you’ve finished making the relevant data transformations, click “Close And Load” in the top-left corner of the Power Query Editor window. Specifically, clicking the top half of the split button automatically loads the data onto a new worksheet, while clicking the bottom half gives you more loading options.

To ensure that the resultant table always reflects any changes in the data source, routinely click “Refresh” in the Table Design tab on the ribbon.

Before you go ahead and use this tool, take a moment to review its strengths and drawbacks:

3

Data Validation: Restrict Data Entries

Whether you use Excel at home or in the workplace, data validation is something that all power users know like the back of their hand. What’s more, beyond fundamental data entry restriction, the data validation tool can be used to power dynamic charts and create cascading drop-down lists.

To get started, select the relevant cell or cells, and in the Data tab on the ribbon, click “Data Validation.” If you see a drop-down menu, click “Data Validation” again.

Then, in the Allow field, select a data type.

For example, clicking “Whole Number” brings up additional fields where you can specify the parameters.

On the other hand, if you choose “List,” you can reference cells or a named range to add a drop-down list to the selected cells.

As well as limiting what can be entered into a cell, Data Validation also lets you add an input message that appears when a cell is selected. Simply head to the “Input Message” tab, and fill in the fields.

Finally, you can define the alert that appears when the wrong data type is entered in the Error Alert tab.

Here’s a summary of the benefits of using data validation in Excel, as well as some caveats you should be aware of:

4

PivotTables: Summarize and Analyze Large Datasets

In their simplest form, PivotTables condense and reorganize large datasets for instant analysis in Excel. After all, worksheets containing hundreds of rows and columns can be overwhelming and difficult to read.

However, once you take some time to play around with them, you’ll realize that PivotTables are an incredibly powerful tool that can do more than you initially thought.

To turn your Excel table into a PivotTable, select any cell in the range, and in the Insert tab, click the top half of the split “PivotTable” button.

To create a PivotTable from a source outside Excel, click the bottom half of the “PivotTable” icon, and select “From External Data Source.”

Then, in the dialog box, confirm that the correct range or table name is selected, and decide whether you want the PivotTable to be added to the active worksheet or a new one.

Now, in the PivotTable Fields pane, click and drag the relevant fields into one of the following areas:

• Rows: These are the fields that sit in the first column of the PivotTable.
• Columns: These are the fields that appear as column labels along the top row of the PivotTable.
• Values: The fields placed in this area are aggregated in the rightmost column of the PivotTable. You can change how the data is aggregated by clicking the down arrow next to the field name once you’ve added it to the Values area.
• Filters: You can filter the whole PivotTable based on a field you add to the Filters area.

To remove a field from a PivotTable area, click and drag it away from the PivotTable Fields pane.

Once you’ve selected the fields you want to be on display in the PivotTable, you can change its layout in the Design tab, or access advanced tools, such as timelines and slicers, in the “PivotTable Analyze” tab.

With your PivotTable set up, you can quickly perform advanced tasks that would take much longer if you did them manually, like showing values as a percentage of the total, filtering the top values, and even creating individual reports based on a filter.

Here are the pros and cons of using PivotTables in Excel:

5

XLOOKUP: Find and Retrieve Data

Finding the right functions to learn if you’re looking to elevate your Excel expertise can be challenging because there are so many. However, if I had to choose one, it would be XLOOKUP.

XLOOKUP searches for a value in a range and returns a corresponding value from another column or row, turning your Excel file into a dynamic workbook with interlinking datasets. Here’s the syntax:

=XLOOKUP(a,b,c,d,e,f)

where

• a (required) is the lookup value,
• b (required) is the lookup array,
• c (required) is the return array,
• d (optional) is the text to return if the lookup value (a) is not found in the lookup array (b),
• e (optional) is the match mode (0 = exact match; -1 = exact match or next smaller item; 1 = exact match or next larger item; 2 = a wildcard match), and
• f (optional) is the search mode (1 = starting at the first item; -1 = starting at the last item; 2 = binary search relying on the array being in ascending order; -2 = binary search relying on the array being in descending order).

In this example, I want Excel to look up the employee ID based on the name in cell H1, and return the result to cell H2.

To do this, I need to type the following formula:

=XLOOKUP(H1,B2:B12,A2:A12,"Invalid name",0,1)

where

• H1 references the value Excel needs to look up (Mary),
• B2:B12 is the array containing the value I’m looking up,
• A2:A12 is the array containing the value I want to return,
• “Invalid Name“ is what I want Excel to return if the lookup value isn’t anywhere to be found in the lookup array,
• 0 tells Excel that I want an exact match, and
• 1 tells Excel to start from the top.

XLOOKUP is a more powerful and flexible alternative to VLOOKUP, which only works with vertical datasets, and HLOOKUP, which only works with horizontal datasets. What’s more, you can use XLOOKUP to perform two-dimensional lookups, returning a value at the intersection of a specified column and row.

Here’s a summary of the strengths and weaknesses of this useful lookup function:

---

To put your Excel expertise under the microscope, have a go at the How-To Geek Advanced Excel Test!