How to Quickly Become an Excel Expert

Co-authored by wikiHow Staff

Last Updated: July 9, 2021 References

Excel is a super helpful program, but its various functions might seem overwhelming! Don't worry. There are lots of tips and tricks you can use to help you complete your work quickly. You can increase your skill set by learning a new set of tricks. You can also learn to use pivot tables to better analyze your data. Finally, spend some time exploring new functions. There are lots of ways to quickly become an excel master!

Method 1

Method 1 of 4:

Learning Tricks and Timesavers

1
Use keyboard shortcuts to navigate faster. Implement keyboard shortcuts and cut out the time it takes to use your mouse. For example, if you hold down the ctrl key while clicking an arrow button, you will select all cells through the end of the spreadsheet. Check out how much faster you can work without your mouse!^{[1]
X
Research source}
- Turn numbers into currency by hitting ctrl, shift, and $ simultaneously.
- Convert numbers into dates by pressing ctrl, shift, and # at the same time.
- Change pages quickly by holding down ctrl while hitting page up or page down, located in the top right corner of your keyboard.
- Generally, Excel for Mac has been updated to very closely resemble Excel for Windows. If you run into problems using these shortcuts, you can check the instructions for your particular program.
2
Get to the Sort menu with a shortcut: command, shift, r. Excel offers custom lists in the Sort menu. You could choose to arrange your produce deliver by week, month, or priority, for example. After you enter this menu, you can click open the "Sort By" drop-down box. There, you can see a wide range of options based on your own spreadsheet. How helpful is that?^{[2]
X
Research source}

Advertisement
3
Press ALT and the Down Arrow to get to the filter menu. This shortcut will allow you to get to the filter menu even faster! You'll see drop-down menus that will let you pick and choose what you want to highlight in your spreadsheet. You can also use several other keyboard shortcuts to use filters even more efficiently:^{[3]
X
Research source}
- Press ctrl, shift, L to turn filters on or off.
- Press ALT, down arrow to display the Filter Drop Down Menu.
4
Locate Quick Analysis in the bottom corner. You can more quickly do basic analysis without having to search for the feature you want. Quick Analysis has a small menu at the bottom right hand corner of your spreadsheet. In this menu, you'll find lots of tools that can be used to pull different parts of your data together. This time saver keeps you from having to search for specific features--lots of them are in this menu.^{[4]
X
Research source}
5
Go to Insert>Charts to find new charts. Charts can make your data look much more interesting! Don't get stuck in a rut of using the same old pie charts. Impress your boss and liven up your data by using hierarchical charts such as tree map. This will show your data as a square (the whole of your data) made up of smaller rectangles which represent the individual components of your data.^{[5]
X
Research source}
- Another newer chart is the waterfall, which illustrates how parts of the data contribute to the whole. You can also find this one in Charts.
6
Make solid predictions using One-Click Forecasting. This tool will make you feel like a pro. Select the data you want to analyze. Go to the Data tab and enter the Forecast group. Choose Forecast Sheet and explore the options in the dialog box.^{[6]
X
Research source}
- For example, you can choose to have your information displayed as either a line or bar chart.
- You can also select the option of forecasting depending on seasonality.

Method 2

Method 2 of 4:

Utilizing Pivot Tables

1
Go to Data in Excel and select Pivot Tables. Mastering pivot tables can be a real game changer for Excel users. Pivot tables will help you reorganize your data and look at it in different ways. Excel will automatically populate your data, but you can move it around as you like. Once you have created the table, you can pick how you want to organize it by choosing one of four options:^{[7]
X
Research source}
- Report Filter, which will allow you to look at only certain rows.
- Column Labels, which can function as your headers.
- Row Labels, which you can use to name your rows.
- Value, which allows you to choose whether you want to count or sum your data and offers various other manipulations.
2
Add multiple rows and columns at the same time. When looking at your table, you can choose which rows or columns you want to add. Highlight the rows/columns you want to insert, right click, and choose “Insert.” This will save time, since this is much quicker than adding individual columns or rows.^{[8]
X
Research source}
3
Pare down your data with filters. When you click on the Data tab in your spreadsheet, choose “Filter.” You can then choose whether you want to view your columns in descending or ascending order by clicking on the arrow next to your column header. You can also do this to choose which rows you want to view.^{[9]
X
Research source}
4
Highlight the column you want to transpose. In some cases, you might want to change your data from rows to columns, or vice versa. Highlight the row or column that you wish to change and right-click. Then choose “Copy.” Select the cell where you want to start your new row or column. Next, right-click and pick “Paste Special.” A new option will appear and you can click “Transpose.”^{[10]
X
Research source}
5
Highlight a row or column to remove duplicates. If you are working with a large data set, you might have a lot of repetition in your data. Select the row or column that you want to refine and highlight it. In the Data tab, look under “Tools” and select “Remove Duplicates.”^{[11]
X
Research source}
- A pop-up will ask you to confirm which data you want to manipulate. Again, select “Remove Duplicates.”

Method 3

Method 3 of 4:

Adding New Formulas and Functions to Your Repertoire

1
Use the Insert Formula tool. Don't be afraid to try some new formulas. To enter a function, highlight the cell that will contain the function. Next, click the Formula tab on the Ribbon at the top of your spreadsheet and choose "Insert Function." If you know the exact formula you want to use, you can type it in. You can also type keywords such as "count cells". Excel can help you find the formula you need, which is really helpful.^{[12]
X
Research source}
2
Practice using SUMPRODUCT in Excel. This function is used to multiply arrays or ranges and give you the sum of the product. The syntax is: =SUMPRODUCT (array1, [array 2],...)^{[13]
X
Research source}
- There are different functions within SUMPRODUCT. For example, if you need to add the bottom values of your data, you can choose the SMALL function within the SUMPRODUCT function.^{[14]
  X
  Research source}
3
Use the COUNTIF function. This is useful if you want to determine how many times a number or word appears in your spreadsheet. The formula is =COUNTIF(range, criteria).^{[15]
X
Research source}
- For example, if you wanted to know how often the word “California” appears, you would type =COUNTIF(B:B, “California”).
4
Use VLOOKUP to combine data. You can think of VLOOKUP as being similar to using a phone book to use a person's name to find their phone number. This function allows you to look at one piece of information and find its corresponding value in the same or different row or column.
- You might find that you want to combine 2 or more spreadsheets into 1. First, make sure that you have at least 1 column that is absolutely identical in both spreadsheets. The formula is: =VLOOKUP(lookup value, table array, column number, [range lookup]).^{[16]
  X
  Research source}
- For example, when you input your own variables it might look like this:
  - =VLOOKUP(C2, Sheet2!A:B,2,FALSE)
  - This would be helpful if you had a part number for a piece of equipment and needed to know the price of the part.

Method 4

Method 4 of 4:

Consulting Other Resources to Learn More

1
Turn to Mr Excel for tricky problems. Even an Excel expert can run into issues. That's okay! Excel has so many features, it would be pretty tough to know them all. If you find yourself stuck, just go to www.mrexcel.com. First, you can try to find the answer to your question on the message boards. Other uses can be really helpful in helping you out.^{[17]
X
Research source}
- If you need expert help, you can sign up for a consult from Mr. Excel. You may have to spend a little money, but you'll likely get a customized solution that can really help you.
2
Take a free online tutorial. Whether you're looking for new shortcuts, or just looking to wow your boss with new skills, a tutorial can really help you get a leg up. GCF Learn Free offers a really thorough course that tackles a variety of Excel issues. You can learn how to more quickly use What-If analysis, and see examples of how to use a lot of different formulas.^{[18]
X
Research source}
3
Do a quick internet search to find a class in your area. In your search engine just type "excel class" and where you live. For example, you could type "excel class in Chicago". For a fee, you can register for in-person or online classes. You'll be able to ask an expert instructor to show you the latest tricks.^{[19]
X
Research source}