There are few people in this world who can claim to have completely mastered Microsoft Excel, the premiere spreadsheet application now in use globally. For over 20 years, it has maintained the status of being the industry standard, since replacing Lotus 1-2-3, which is no longer supported but was actually quite popular in its day. Microsoft Excel has become dominant because it is quite powerful and does almost everything you could ask for in a spreadsheet.

With time, it has also become one of the easiest apps to use, but even many experienced users are still unaware of how to use Microsoft Excel to its full potential and may benefit from additional Excel training. If that is the case with you, start today by taking advantage of the following simple tips for Excel power users:

1.                  Create a table with a shortcut

Tables are one of the most useful features in Excel for displaying data that’s in connecting rows and columns. With tables, it becomes easier to visualize, filter and sort data. When new rows are added, they automatically have the same format as the ones above them. Also, if you use the data in a table for making charts, the chart will be updated automatically when you add new rows to the table. There is an easy keyboard shortcut that you can use for creating a table in Excel. First, select all of your data with Ctrl-A.

Then press Ctrl-T to turn it into a table.

2.                  Add a summary row to the table

When your data is formatted as a table, the Design ribbon appears in Microsoft Excel, which allows you to add a summary row to your table by clicking on Total Row.

By clicking on the drop down at the right hand side of a Total Row cell, you can set the information to display in different formats such as standard deviation, count, average and more.

Of course, you could always manually set a normal row to display total information using a formula, but when you select Total Row in the Design tab, it will update automatically when you insert new rows and stay at the bottom when you perform data exploration such as sorting and filtering. However, bear in mind that Total Row needs to be made individually for every column unless every column has the same type of data.

3.                  Use slicers for filtering table data

Drop down arrows are displayed by Excel tables next to every column header for easy searching, sorting and filtering. However, when you have a large number of items and you are filtering data with a small drop-down, it can become hard to manage. Instead, you can use slicers, which provide a more visual and easier to manage way of filtering data. While they were originally developed for pivot tables, slicers can now be used on regular tables as well. You can add a slicer by heading to the Design ribbon and clicking Insert Slicer.

Then selecting the columns you want to filter.

You will see the slicer on your worksheet, which will be one column wide and show a few items.

However, you can easily scroll through the items and resize the slicer to filter your data more easily.

4.                  Make a summary cell that changes with filtering

Let’s say you use a cell outside of the table for summarizing data, like the sum of a column, and you want the updated sum to be displayed after you have filtered the data. A basic SUM formula is not going to be effective. Instead, you are better off using the AGGREGATE function in your cell and then linking it to the table filters.

For the AGGREGATE function to work, you need to select your desired function number to specify how you want to display the data (e.g. SUM, AVERAGE, MAX, etc.), an option number to specify which rows to ignore (e.g. error values, hidden rows, etc), and the range of cells you wish to operate on.

However, you don’t have to remember all of the function and option numbers – just type =AGGREGATE( and the available options and functions will appear.

5.                  Sorting data in a pivot table

Just like with a regular table, sometimes we want to sort data in a pivot table by a specific column. However, as opposed to regular tables, you will not find dropdown menus on each column in pivot tables that give you the ability to sort. But, if you go with the lone dropdown arrow that’s found on the first column and click More Sort Options, you will get access to a menu that lets you sort by any column.

6.                  ‘Unpivot’ the data

This is often considered to be reshaping the data from wide to long. This is known as ‘fold’ in the database world as you take the data from individual columns and put them into rows. In simple terms, it is exactly the opposite of making a pivot table. You can ‘unpivot’ the data by using the Query Editor that can be found in Excel 2016. The Data Ribbon lets you gain access to the Query Editor. When it comes up, you can choose the sections you are interested in ‘unpivoting’, select the option of Transform tab and choose Unpivot Columns.

7.                  Make several pivot tables for one column

When you have a pivot table, you can generate several copies of it by adding a filter for every column that contains categories. A pivot table will be created for every category in your filter; all you have to do is go to Analyze, choose Options, go to Show Report Filter Pages and select the filter you want. This can be better than clicking through every category in the filter manually.

comments