Make Quick Work: These 10 Excel Skills Save the Most Time.

• Author: Lemming, Jeff

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.

Power Query

Power Query (excel-university.com/tag/power-query helps us gel 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.

PivotTables

PivotTables (excel-university.com/tag/pivottable) 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.

Power Pivot

Power Pivot (excel-university.com/tag/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.

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 (excel-university.com/tag/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.

VLOOKUP

The VLOOKUP (excel-university.com/tag/vlookup) function is designed to...