Make Quick Work with 10 Excel Skills

Jeff Lenning | September 18, 2019 | Comments Off

Excel is big … really big. It has hundreds of functions and features, but which of these have the most potential to save time and help us work faster? The answer depends on what you’re working on. For common accounting and finance tasks, I believe the following 10 Excel skills can help improve the efficiency of recurring-use workbooks.

Some of these are simple to use and quick to understand; others take time to learn. I chose topics that have the potential to save more time than they take to learn.

1 Power Query
Power Query helps us get and transform data. That is, it enables us to retrieve data from various sources and define a series of steps to prepare and clean it—such as removing excess columns, filtering for specific rows, adding calculated columns and more. After defining the steps, we can simply click Refresh in future periods to update the results as the source data changes.

2 PivotTables
PivotTables are reports that summarize source data and are traditionally used to summarize data in a single table. However, modern PivotTables enable us to summarize data from multiple tables and include robust formulas. In addition to a single table, modern PivotTables can use the workbook’s data model as the data source. Data model? Yes, think of a workbook’s data model as the place we organize multiple tables and write formulas.

3 Power Pivot
Power Pivot enables us to manage the workbook’s data model. Think of it as a place to organize tables and define relationships between them. Plus, we can write formulas and include the results in our reports. When you combine Power Query and Power Pivot to create PivotTables, you streamline the data flow from the source to the final report. All of which can be refreshed with a mouse click.

4 SUMIFS
If the report you’re trying to build, such as a complex financial statement, income statement or balance sheet, doesn’t really fit inside a PivotTable, SUMIFS can help prepare a formula-based report instead. This function is a conditional summing function that enables us to summarize table data. Since the function is used in formulas, we can place the results in specific cells as desired.

5 VLOOKUP
The VLOOKUP function is designed to retrieve a related value. This function is often cited as the most popular Excel function, and for good reason. It has many applications and is a critical Excel skill.

6 INDEX
The INDEX function can return a cell value like VLOOKUP. It can be a nice alternative, depending on the nature of the workbook and data.

7 MATCH
The MATCH function returns the relative position of a list item and is a wonderful helper to both VLOOKUP and INDEX.

8 SUBTOTAL
The SUBTOTAL function adds up a range of values and excludes other SUBTOTAL functions in the range. This is especially helpful for formula-based reports that have subtotals such as balance sheets.

9 Tables
Tables are a great way to store expanding data. When we append new rows to a Table, it automatically expands to include them. As new rows are appended to the Table, our formulas don’t need to be updated to include them.

10 Visual Basic for Applications
VBA is the language used to write macros. Do you have any recurring-use workbooks that include a set of “instructions” for the user? These instructions may document the sequence of steps needed to update the workbook for the current period. Instead of writing instructions for the user (e.g., in English), you can write instructions for Excel in VBA. That way, Excel will perform the routine tasks for you each period.

So, if you are looking for ways to save time with Excel, these 10 skills can help. And remember, Excel rules!

Related Excel University Blog Posts