Overview
If you’re new to Excel, congratulations! It’s one of the most important applications ever built, and Microsoft keeps making it better. Whether you’re going to be a power user in data analysis, finance, accounting, etc. or just a casual user, there are a few things you absolutely must know. Consider this your learning plan for the next three months. Don’t stop there – you’ll be amazed what you can do with it! Bookmark this page and search the site for a ton of great Excel resources, tools, and templates. Want more? Check out our Top 50 Next-Level Excel Features e-book after you browse through the ones below.
The Top 10 List
OVERALL STRUCTURE
First thing’s first…make sure you know the core structure of Excel – how files work and how they are laid out. Good news – this is pretty quick. Don’t memorize every menu command, but scanning through the different ribbon tabs and right-click menus is helpful so you’re generally aware of where things are.
Pro tip: Name and save your files consistently. I like to put a date at the end of every file name in a format like this: 2023-12-06 – that way you can easily search the rough time period and bounce around the adjacent months and often find the file, even if you don’t know the exact file name.
BASIC NAVIGATION
Navigation is something you’ll spend quite a bit of time doing! The quicker you get at moving around and selecting data among sheets and cells, the more time you’ll save and the more energy you’ll have to focus on valuable analysis.
Pro tip: Learn 10+ keyboard shortcuts for navigating (like the ones in the graphic above).
CELL REFERENCES
Cell references are a hugely powerful part of Excel. They power most formula functions and allow you to manually enter data one time, then reference it over and over again.
Pro tip: Limit your manually-entered data to an Inputs sheet. Separate your source data and the output format you prefer, and link everything together with cell references.
BASIC FORMULA FUNCTIONS
There are hundreds of functions in Excel. Most users don’t need to know but maybe 50 of them, starting with the ones in the graphic above. Don’t be afraid to try out some of the formulas that aren’t used as often – they are mostly quite self-explanatory.
Pro tip: Once you’re comfortable, try out some of the new dynamic array functions – they can automatically “spill” over into other cells dynamically as your data changes!
Resource: Get a workbook full of examples, tips, and gotchas for the top 60 Excel functions to know here.
FORMATTING
Formatting is another area where you’ll likely spend quite a bit of time in Excel. Excel offers a variety of formatting options to make your files look nice and polished, and easy for others to understand.
Pro tip: Once understand the basic formatting options, check out conditional formatting. It’s extremely useful for identifying errors and specific values in your data. There are several built-in conditional formatting options you can quickly apply.
DATA ANALYSIS
There are several tools that help you analyze and present data in Excel – this is where Excel really shines. Filtering is an especially common task for most users. PivotTables are a quick way to aggregate and slice and dice your data in an easy drag-and-drop format. While they can be frustrating to set up just right, charts are awesome for understanding the story behind your data. I prefer using PivotCharts most of the time because of how easily they can be set up and adjusted.
Pro tip: Once you are comfortable with filtering, check out slicers. They are a relatively new feature that give you a visual to understand how your data is filtered.
TABLES
An important approach in Excel is using tables (or at least a tabular structure – field headers at the top, records/values in the rows below) for your underlying data. This gives you the ability to use data analysis features and leverage many of the various formula functions the expect data in a tabular format.
Pro tip: Using proper tables (as displayed in the graphic above) automatically adds filters, banded background colors, and a more readable referencing structure!
COPYING AND PASTING
Copying and pasting may seem a little straightforward, but in Excel, there are a lot of different ways you can paste data or formats. A key notion to learn is how copying and pasting cells works with formulas and the different reference types. Excel makes it easy to write a formula and copy and paste it all throughout the sheet without having to adjust for the different columns and rows.
Pro tip: Check out the paste special menus on the ribbon and the dialog box. Some helpful, lesser-used options include pasting column widths and pasting as a picture!
KEYBOARD SHORTCUTS
Keyboard shortcuts are proven to be 3x faster than using the mouse, so for any apps you use frequently, it’s worth learning several of them.
There are hundreds of keyboard shortcuts available in Excel. You probably know some of the basic ones like Ctrl+c for copy and Ctrl+v for paste, but there are so many common actions you’ll take in Excel, and keyboard shortcuts are the quickest way to execute them. Pay attention to the commands you use most and make an effort to learn the keyboard shortcuts for them.
Pro tip: Two super shortcuts you should learn quickly are Alt key and the Menu key (normally near the right side of the spacebar).
Alt lets you follow along the ribbon menus with a string of characters. The Menu key mimics the contextual right-click menu.
Resource: Get a workbook full of keyboard shortcuts for Excel, Word, PowerPoint, Outlook, File Explorer, and Google Chrome here.
MACROS
One of the most powerful Excel features is the ability to leverage macros to extend the Excel feature set and automate commands. Even better is the ability to record your actions to build your own macros (that’s how I got started!). Virtually any repetitive task or series of steps can be automated or condensed into fewer steps by leveraging macros.
Pro tip: Want to leverage the power of macros, but don’t want to build them? There are tens of thousands of templates and add-ins you can leverage containing professional-grade functions that help you work more efficiently or do things you couldn’t otherwise do in Excel.
Resource: Check out the XLEV8 Excel Add-in, which contains 350+ macros across the five categories in the graphic above that help with all the must-know Excel features above!
Summary
Most experts would agree that with Excel comes a lifetime of learning. There are so many creative approaches, tips, and tricks that you’ll find as you go. There are often several solutions to the task you’re trying to complete, and the best one could be different depending on the scenario and your audience. And lately, Microsoft has been adding some phenomenal new features that make it easier and more powerful than ever.
What are some of the features you wish you had known when you first started using Excel? Let us know in the comments below!
Recent Comments