Data structured in a table format often makes it easy to analyze it and make better decisions. Data analysis in Excel can be done by using formulas like COUNTIFS, SUMIFS, and AVERAGEIFS, but Excel’s PivotTables make it a breeze organizing and presenting data.

In this article, we’re going to compare the two approaches for analyzing data in Excel, using formulas and PivotTables.

# Creating a report

Let’s assume we have a spreadsheet that contains employee data, and we want to find the average base salaries by department, to create an annual report.

You can download this data set by clicking the button below.

We can calculate this by using a handful of AVERAGEIF(S) functions. However, data needs to be organized first for use in AVERAGEIFS functions.

First step is getting the *department* names from the table and remove the duplicate values to get a list of unique items. To learn more about finding alternative ways to find unique values see: How to find unique values Excel. Here, we’re going to copy the all D*epartment* column, paste it using the *Remove Duplicates* feature.

Next, we’re going to tweak our table a bit and add some formatting for a better presentation. Here are some ideas and features to utilize to create a nice-looking report:

- Sorting the
*department*names - Add titles
- Summarize row
- Bold characters for title and total rows
- Background colors
- Borders
- Adjusting to column widths
- Number formatting that matches the type of the data (e.g. Currency)

Finally, we can start adding the formulas. We can use references from the *Department* names in **AVERAGEIFS** formulas when selecting department names. We could also use the AVARAGEIF function, but the AVERAGEIFS function can accept more than one criteria and this is generally a better approach from a future-proofing perspective.

We used named ranges to make the formulas easier to read and manage. While *Base_Salary* named range represents the range that contains the base salary values, *Department* represents the column of department names.

The last step is adding a formula for summary calculation which will give us the averages. Dividing the sum of *base salary* values by the count of rows gives the average values we need.

Let’s now analyze the same data using **PivotTable**. Begin by selecting the cell to be the top left cell of your **PivotTable** and click the *PivotTable* icon under the *INSERT* tab on the ribbon.

Add the reference for your data into the *Create PivotTable* dialog box and click *OK* to create your PivotTable.

Next, we’re going to add the columns we want to display and add number formatting. That’s it!

The PivotTables are very easy to create and configure. You do not need to add any formulas and data analysis features can be added directly through table controls. PivotTables add the relevant calculations automatically.

An important part of data analysis in Excel it to drill down into our data. Assume that we have to add a 2^{nd} level of filtering to see the breakdown by *states*. Doing so using formulas, we need the following:

- Adding columns if there is additional content at the end of the table
- Update the format like borders, background colors and number formats.
- Repeat the
*copy-paste*process and*Remove Duplicates*for the*state*values - Edit the titles

Modifying the existing formulas, we will end up with the updated calculations.

This step requires a closer attention. We must use an Absolute-Relative reference combination to preserve the calculation results. You might also want to add an **IFFERROR** function or conditional formatting to prevent error messages from being displayed for empty cells.

After dealing with the possible errors, once again, we need to update the formula to calculate values for the summary row. Finally, our table shows 2 levels of filtered consolidated values of our base data. Let’s now see how a 2^{nd} level of data can be added into this **PivotTable**.

All you need to do is drag & drop the secondary data. You can change the calculation logic or filtering with a few clicks. Filters, also known as slicers can also be added with a click.

*Slicers* are filters of **PivotTables** that can be linked multiple **PivotTables** and **PivotCharts**. You can insert any column of your data as a *slicer* and filter your data. If you want to do this using formulas, you need to use the controls from *DEVELOPMENT* tab, and update all formulas.

# Other advantages of PivotTables over using data analysis formulas

- They are faster, especially with large datasets.
- Formulas can use at most 127 conditions, PivotTables can handle much more as much as memory allows.
- Easy to apply visualization options.
- PivotCharts that can visualize data are just as easy to create as PivotTables
- You can group any data field without any additional formulas.