Excel is one of the most used tools in almost many organisations. However, most people just know the basics of this incredible software, which makes them less efficient. Just knowing the basics is okay for regular tasks. But when you are handed a large amount of data and have to do complex calculations and data visualisation, then you need to upgrade your Excel skills to an advanced level.

Here are five important advanced Excel skills that you should master in your professional life to become more efficient and handle data better. They can also help you get certified in Microsoft Excel. 

PivotTables

Data analysis is one of the primary fields in which Microsoft Excel is regularly used. Therefore, if you want to be an expert at Excel, then you will have to be familiar with skills that help you analyse large amounts of data. You can group, filter, and rearrange information with just a few steps to gain deeper insights without altering the original data.

Let’s walk through the process of creating a PivotTable to summarise data.

1. Select Your Data Range: First, choose the group of cells holding the information you want to analyse. As field labels in your PivotTable will be derived from your dataset, ensure it includes column headers.

Figure 1: Data table with proper column headers

2. Insert a PivotTable: Go to the “Insert” tab on the Excel ribbon and select “PivotTable”. 

Figure 2: PivotTable option under the “Insert” ribbon

You will see a dialogue box where you can confirm the range of your data. The dialogue box will also have the option to select the location where you’d like to place the PivotTable—either in a new worksheet or an existing one.

Figure 3: Dialogue box for PivotTable

3. Set Up Your PivotTable Fields: Once your PivotTable is created, you’ll see the “PivotTable Field List” on the right. Here, you can drag fields to different areas—Filters, Columns, Rows, and Values—to customise how your data is displayed.

• Filters: Dragging a field here allows you to filter the entire PivotTable based on the selected field.
• Columns: Fields here are displayed as column headers in the PivotTable.
• Rows: Fields placed here are displayed as row headers.
• Values: Drag a field to this area to display a summarised calculation, like Sum, Average, Count, etc.

4. Customise Your Data Summary: You can adjust the summary function by clicking on any field in the “Values” area and then selecting “Value Field Settings.” Here, you can choose to display sums, counts, averages, or other calculations for that field.

In our case, we selected Salesperson, Units Sold, and Revenue to find the sums. 

Figure 4: PivotTable’s data analysis

5. Refresh Your Data: If you update your original data, right-click on the PivotTable and select “Refresh” to ensure it reflects the latest changes.

Figure 5: Updating a PivotTable

And then that’s it! In a few easy steps, you have created a dynamic and easily adjustable PivotTable that lets you examine your data from several angles.

Macros and VBA Automation

Let’s say you always have to do similar tasks in Excel. In this case, macros and VBA (Visual Basic for Applications) can be the skills you need in your professional arsenal. A macro is like a template that Excel can follow to automate a specific task. It’s like teaching Excel what you do by recording your tasks and then letting Excel handle it the next time. 

VBA, on the other hand, is a programming language that you need to create macros. To run macros, you need to use the “Developer” ribbon. Learning macros is a bit more complicated, hence we included it as an advanced skill. You can enrol in Sikkhon’s online courses on Excel to learn about such skills from experts and become a master Office user.

Conditional Formatting

Visualising data is a big part of using Excel at an advanced level. With Conditional Formatting, you can see certain trends in complex data, highlight important information, and identify patterns easily. For example, let’s say you have a dataset of a city where you have listed the prices of different neighbourhoods. With the help of Conditional Formatting, you can visualise which places in the city are the cheapest and which ones are within or over a certain budget. 

Here’s how to get started with Conditional Formatting:

1. Select the Range for Formatting: Choose the cells you want to use Conditional Formatting on first. Depending on your need, this range may be a single column, row, or a whole table. In our example, we selected the “Revenue” column.

Figure 6: Selecting the “Revenue” column for Conditional Formatting

2. Open the Conditional Formatting Menu: Go to the Home tab on the Excel ribbon, and click Conditional Formatting in the Styles group. Here, you’ll see various options, including pre-set formatting rules and the option to create custom rules.

Figure 7: Conditional Formatting menu

3. Choose a Formatting Rule: There are several built-in rule types you can choose from:

• Highlight Cells Rules: Use this option to highlight cells that meet specific criteria, such as those containing values greater than or less than a certain number or specific text.
• Top/Bottom Rules: Highlight cells representing the top or bottom values within the selected range, such as the top 10% of scores.
• Data Bars: Add gradient bars inside cells to visually represent the magnitude of the values.
• Colour Scales: Use a colour gradient to show the distribution of values across the selected range, which helps spot trends.
• Icon Sets: Display icons like arrows or check marks representing different data ranges.

4. Apply a Highlight Cells Rule: Let’s say you want to highlight all cells with revenue over 400. Select Highlight Cells Rules > Greater Than and enter the value 400. Choose a colour for highlighting, then click OK.

Excel will now highlight any cell in the selected range with a value greater than 400

Figure 8: Using Highlight Cells Rules to visualise data

You can also use a custom Conditional Formatting Rule if simple formulas will not work.

Advanced Formulas and Functions

Formulas and functions are two of the things that make Excel such an invaluable tool when it comes to working with data. They allow you to do anything from the most basic calculations to complex maths with a large set of information. 

Check out our Advanced MS Excel Formulas and Functions blog to learn more about some of the most used formulas for this tool, including conditional logic, array formulas, and so on. 

Power Query and Data Management

Power Query is used when you have multiple sources to work with. It helps you connect, combine, and clean data. The Power Query option is available on the Data tab on the ribbon. There, you will be met with the “Get & Transform Data” group on the left side, where you have the following options:

• Get Data
• From Text/CSV
• From Web
• From Table/Range
• From Picture
• Recent Sources
• Existing Connections

Final Words

These five are just the start! There are many more advanced Excel skills to learn if you want to become an expert. What better way to master such skills than learning from an expert themselves? Consider enrolling in Sikkhon and learn Excel and other Microsoft tools from the best.