How to Master Excel in 12 Steps

Microsoft Excel is one of the world’s most popular business software packages. It is incredibly powerful, and its features regularly get an upgrade to make it even better.

You might be trying to learn how to master Excel — regardless of your job role, Excel can help you work quicker, smarter, and more efficiently. This article will guide you through 12 of the best tips and tricks to help you harness the power of Excel and impress your boss and work colleagues.

After going through these steps, you can also download our free Excel for beginners guide to reinforce the basics at your own pace. 

How to master Excel fast in 12 steps

1. Get comfortable navigating the interface

Need a place to start? Here’s our recommendation: learn how to navigate the Excel interface.

Let’s start with the basics:

When typing data into Excel, you can use the Tab key to move to the next cell in the column to the right.

You can use Enter to move to the cell in the next row down. If you have been using Tab to move through the columns, pressing Enter will take you one row down and back to the cell in the column you started in.

Pressing the Ctrl key and the Up, Down, Left, or Right directional arrow keys together will take you to the last used cell in that direction. This is useful for navigating large lists in Excel.

Pressing Ctrl + Home takes you to the beginning of the worksheet (cell A1).

2. Learn some useful shortcuts

The best way to speed up your day-to-day Excel work is to learn useful shortcuts with both the keyboard and your mouse or touchpad.

If you’re a beginner, start with the basic keyboard shortcuts. Some good ones to learn are the famous copy and paste: Ctrl + C to copy and Ctrl + V to paste. Plus, there’s Ctrl + Z to undo the last action — great for any accidental actions!

As you begin to use these Excel shortcuts in your daily work, push yourself to learn more.

Two of my favorite shortcuts are Ctrl + ; to enter today’s date in a cell. Another favorite is double-clicking the fill handle (the square in the bottom-right corner of the cell) to copy down a list.

Excel has hundreds of keyboard shortcuts and plenty of useful tricks to help you work faster.

Although you won’t need them all, learning some to make your Excel work easier is a great idea.

3. Freeze panes

Freezing panes is a handy Excel feature that keeps your headings and other labels visible at all times as you scroll around the spreadsheet. It’s an essential skill to know.

The headings for a spreadsheet are usually just in the top row. So to freeze this area, click View > Freeze Panes > Freeze Top Row.

In the image below, we are looking at row 1259, but the headings are still visible on screen.

You can freeze as many rows and columns as you need.

Click in the first cell after the rows and columns you want to freeze. For example, if you want to freeze 3 rows and 1 column, click on cell B4.

Click View > Freeze Panes > Freeze Panes (the first option).

Now when you scroll down, the first 3 rows remain visible. And if you scroll to the right, column A remains visible.

4. Understand Excel formulas

One of the main ways to master Excel is to be accomplished at writing formulas. These are the muscles of Excel.

Whether it’s performing basic calculations in cells or using more advanced formulas, if you know how to use these functions, you will stand head and shoulders above the rest!

If you aren’t sure where to begin, start with creating basic calculations that add, subtract, multiply, and divide values. Then begin to learn some of the more commonly used functions. These include SUM, IF, XLOOKUP, COUNTIF, and CONCAT. If you’re using Excel 2016 or 2019, you can use VLOOKUP instead of XLOOKUP.

Once you are comfortable writing formulas, you can do almost anything. You can even use formulas within Conditional Formatting rules, charts, and other Excel features to make them more powerful.

Check out some of our favorite Excel formulas and functions.

5. Learn how to create a simple drop-down list

A simple Excel dropdown list on a spreadsheet can make entering text much easier and, more importantly, ensure it is accurate.

To create an Excel dropdown list:

1. Select the range of cells you want the list to appear in.
2. Click Data > Data Validation.
3. Select List from the Allow list.
4. Then either type the list items into the Source box, separating each one with a comma, or select a range of cells that contain the list items.

Now you have a drop-down list, making data entry simple and reliable.

6. Visualize key data with conditional formatting

Conditional formatting is one of the most popular features of Excel. It helps the user get a quick understanding of the data they are looking at.

You can create simple conditions to automatically format cells if a target is reached, a deadline has passed or maybe sales have decreased below a certain threshold.

For a quick example, let's say we want to change the cell color to green if a value is greater than 300.

• Select the range of cells that you want to apply Conditional Formatting to.
• Click Home > Conditional Formatting > Highlight Cells Rules > Greater Than.

• Enter 300 and select the formatting you want to apply.

A green fill and font color are used here, but there are lots of formatting options available.

With conditional formatting in Excel, you can also apply data bars and icon sets. These visuals can be very effective. The image below shows data bars applied to the same range of values.

7. Get things done faster with Flash Fill

Flash Fill is an awesome Excel feature that quickly manipulates data. This feature can greatly reduce the time taken doing regular data cleansing tasks, which previously we relied on Excel formulas and macros to do.

Take, for example, the list below. We want to extract the name from the list in column A.

Enter the first name in cell B2 and then the second name in cell B3. As you type, Flash Fill recognizes the pattern and suggests filling in the remaining rows. This is amazing!

Press Enter to accept the suggestion, and voilà! The names are extracted.

There are several ways of running Excel Flash Fill. You can also find it under Data > Flash Fill, or use the shortcut Ctrl + E.

8. Learn visual storytelling with charts and graphs

Learning how to represent numerical data visually is definitely a skill you should master if you want to be proficient in Excel. Not everyone will want to inspect and compare endless rows of data for patterns and trends. Mastering Excel data visualization will make it easy for you to highlight the most important bits of information in your dataset.

Here’s how to create a graph in Excel in 3 easy steps:

• Highlight the source data.
• Go to Insert on the Excel ribbon and select the type of graph you want.

• Use the available customization options to format the chart the way you want.

Chart customization

The ways in which you can customize a chart in Excel are virtually endless. The Chart Design tab is contextual, meaning it only appears when a chart is selected. Using this tab, you can change the style, source, or location of your chart, and you can also add elements like a legend, data labels, and axis titles.

You can even change the type of chart used to represent your data if you think another chart will do a better job drawing attention to the story you want to tell.

You can even change the type of chart used to represent your data if you think another chart will do a better job drawing attention to the story you want to tell.

🚀 Need to learn the basics of Excel fast? Our free, 1-hour Excel crash course is a great place to start!

9. Summarize data with PivotTables

PivotTables are some of the most amazing tools in Excel. They make summarizing large datasets as easy as 1, 2, 3.

An Excel PivotTable could be used to break down an extensive list of sales to see the sales by region or even sales of particular products in each region.

They’re extremely powerful, but also simple to use. A PivotTable is an Excel tool that makes creating reports more straightforward and doesn’t require writing any complex formulas.

To create a PivotTable:

• Click on the list of data you want to summarize.
• Click Insert > PivotTable.
• In the Create PivotTable dialog, ensure the range being used is correct and specify whether you want the PivotTable on a new or existing worksheet.

• Drag and drop fields from the field list into the four areas to create your Excel PivotTable.

In the image below, the years are in Columns, the product category in Rows and the total sales value in the Values area.

The Excel Values area is where calculations such as sum, count and average are performed.

The calculation can be changed by right-clicking a PivotTable value, selecting Summarize Values By and choosing the calculation you want to use.

There is plenty more that Excel PivotTables offer, but that is beyond the scope of this article.

With PivotTables, I encourage you to grab some spreadsheet data, insert a PivotTable and start to drag fields into the areas and explore the options available. Excel really is a tool that you learn by doing. You can also try our Excel practice exercises to put your skills to the test.

10. Learn how to protect worksheet data

Excel features such as PivotTables, formulas, and conditional formatting will only work if the data is accurate.

A few of the tips in this article demonstrate ways to maintain this accuracy, including creating drop-down lists and using Flash Fill. Protecting your data is another important step to take.

Excel has a few different types of protection that can be applied, but the most popular one is to protect the sheet.

Protecting a sheet is performed in two stages. Firstly, you specify which cells on the worksheet you do not want to lock. This ensures that users can still edit these cells. Then you apply the protection.

1. Select the range(s) of cells that you want to unlock (hold down the Ctrl key to select multiple ranges at the same time).
2. Press Ctrl + 1 to open the Format Cells dialog.
3. Click the Protection tab, uncheck the Locked box and click OK.
4. Click Review > Protect Sheet.
5. You can add a password to prevent others from removing sheet protection, but it’s optional. If you’re just protecting against accidental edits, you may not need one. Check the boxes for any functionality users will need and click OK.

Keep in mind that sheet protection helps prevent unwanted changes, but it isn’t designed to secure sensitive information.

You can still edit unlocked cells, but locked cells are protected from changes. If you try to edit a locked cell, Excel will display a message telling you that the sheet is protected.

11. Power up with Power Query and Power Pivot

Having knowledge of these two Power tools will take you beyond the regular user.

Power Query is a tool used to import and shape data ready for analysis. It is found on the Data tab of the Ribbon.

With Power Query, you can easily import data from a variety of sources, including CSV files, the web, and folders.

The Power Query Editor then provides a user-friendly environment to perform a number of cleaning and shaping operations, such as splitting columns, formatting, removing duplicates, and unpivoting data.

Power Query uses a language called M, but you don't need to learn it to perform most everyday data-cleaning tasks. The editor handles these operations for you.

Every edit that is made is stored as a step and can be refreshed in the future with the click of a button.

Power Pivot is an Excel add-in that helps you build and manage a Data Model, allowing you to work with much larger amounts of data than a regular worksheet can hold. It's available in supported versions of Excel for Windows, but not Excel for Mac.

In Power Pivot, you can also create relationships between multiple tables and write formulas using DAX (Data Analysis Expressions).

The ability to create these models and use DAX provides far more power than you can get from a regular spreadsheet alone.

This is an important tool to understand for anyone involved in analyzing huge quantities of data or who needs to perform complex analysis

Bonus: Check out Ken Puls’ lesson from his Power Pivot course, which explains what Power Pivot is and how it can streamline your workflow.

12. Create Macros with VBA

Macros in Excel enable you to automate repetitive tasks to supercharge your productivity.

These tasks can be complex, but for most users, they are reasonably simple tasks that are repeated often. Macros provide a way to perform tasks quicker and more reliably.

You can get started with macros by recording yourself performing Excel tasks. This will generate the VBA code and produce a macro.

You can find the macro recorder under View > Macros > Record Macro. It's also available on the Developer tab, which is hidden by default. To enable this tab in Excel for Windows, go to File > Options > Customize Ribbon and select Developer.

Recording Excel macros can save you and your colleagues a great deal of time.

If you want to take things further, you can learn Excel VBA and edit your macros.

With VBA, you can make your macros do things way beyond recording and create functionality that Excel does not offer itself.

To see the VBA code created by a recording:

• Click View > Macros, select the macro in the list and click Edit.

Once you’ve started recording a few macros, you’ll probably want to roll your sleeves up and learn how to start writing VBA code on your own. If this sounds like something you might be ready for right now, check out our specialty Macros and VBA course.

Want to master Excel?

Of all the tips on how to master Excel, one of the best ways to build your skills is to take a structured online Excel course that guides you step by step. Practice alongside a Microsoft MVP and learn practical skills to boost your workplace efficiency!