In Association with
5 essential skills to become an Excel guru
Microsoft Excel is one of the most powerful programs in the Microsoft Office suite, able to assist you with accounting, presentations, reporting, marketing, and more. It’s also one of the most complex office products to learn. From basic formulas to organizing data, working with pivot tables, lookup functions, and Power Query, it can be overwhelming figuring out where to start.
We compiled a list of the five essential skills Excel users need to master to wow management, create reports, analyze data, and deliver insightful presentations. Of these five tips, the first two are for absolute beginners. If you’re a seasoned Excel veteran looking to brush up on your skills, skip to Step 3. Otherwise, read on for an introduction to one of the most capable programs in your Office toolkit.
1. Brush up on the basics
If you’re a complete Excel newbie, the program can be intimidating, but learning Excel doesn’t have to be hard. When you open a new Excel file, you’ll see a large, seemingly infinite grid marked with letters on one side and numbers on the other. Using this system of letters and numbers, you can identify any cell in the grid. For example, the cell in the top-left corner is A1, and the cell to its right is B1, and so on.
You can fill this table with whatever data you want by typing it into a cell. At its most basic, you can use Excel to conveniently store any information and organize it in a table.
In addition to storing and organizing data, you can quickly sort it in alphabetical order using the Sort button from the Data tab, or filter by specific entries using the Filter button. If you need help learning the basics, use Excel's Help menu, which you can access from the Help tab located at the top-right.
2. Master presentation
Presenting data is another vital Excel function. Let's say you have a list of customers, with some living in New York, others in London, and others in Sydney. With just a few clicks in Excel, you can organize this data set into an attractive chart, with pie slices or bars of differing sizes to represent the proportion of customers living in each city.
You can create a graphical representation of almost any kind of data in Excel, which has 20 built-in graph types, including bar charts, pie charts, and line graphs. To learn how to use them, start with Excel's built-in help, then explore some online courses.
Udemy's Microsoft Excel – Data Visualization, Excel Charts & Graphs course provides a deep dive into data visualization. You will learn exactly when and how to use Excel's 20 different chart types using hands-on exercises, and build 10 projects that will take you from absolute beginner to data visualization superstar.
3. Use pivot tables
In the world of Excel, creating a proper pivot table is a rite of passage, separating casual from advanced users in the eyes of recruiters, managers, and industry professionals.
A pivot table is another form of data visualization used to summarize vast sets of information in a concise and visually appealing way. Unlike a chart, a pivot table exists within the Excel grid, so you can also reference cells in the table for further calculations.
You can learn to make basic pivot tables using Excel’s Help section, and numerous free online resources exist to help you learn advanced functions. If you prefer the structured environment and accountability of a course, Udemy offers an affordable tutorial that goes in-depth on Pivot Tables, Power Pivot Tables, Power Query & Power BI.
4. Learn formulas and functions
One of Excel's most powerful features is the ability to run calculations directly from the chart. To do this, type the = character into any cell, then type in the formula you want to use. For example, the formula =SUM(A1:A6) will output the total sum of all values in cells A1, A2, A3, and so on to A6.
Excel has hundreds of formulas. They help you do everything, from mathematical operations to finding specific information, to advanced sorting, and even projecting data from one Excel sheet to another.
Every user should know how to use basic formulas like the SUM function above, but if you want to delve deeper and create truly insightful reports, we recommend an advanced Excel course.
Udemy's Microsoft Excel – Advanced Excel Formulas & Functions course will show you how to use Excel's most advanced, time-saving formulas, while presenting complex information in novel ways.
5. Expand your experience with Power Query
You may have noticed that the course we recommended in step three mentions Power Query. This add-in used to be separate from the default Excel Suite, but as of Excel 2016, you can access it directly by clicking Get Data in the Data tab.
Power Query enables you to pull data from different sources, transform that data to suit your needs, combine it with other data sets, and load it into one large Excel model. You can, for instance, create a complete financial report that pulls data from one file with revenues, another with expenses, and another with accounts receivables.
Think of Power Query as the most powerful and complex tool at your disposal, and use it when needed to create large, interconnected data models in Excel.
Summary
Once you’ve mastered the nuances of Power Query, learned about Excel's most useful formulas, and gained intuitive command of pivot tables and data visualization, you can truly call yourself an Excel guru.
To help you along on your journey, see our list of the best Excel online courses. But why limit yourself to Excel? Read our list of the best spreadsheet software to see other tools on the market for data management and visualization.
