Traci Williams shares some essential shortcuts and tips to help you work with Excel faster and smarter
Microsoft Excel is often the first tool we all turn to for data analysis, financial modelling, and general spreadsheet tasks. It is so versatile that it can adapt to most of our needs, from simple calculations to complex data manipulation and calculations.
However, it can feel frustrating when you watch someone else working with it and they seem to make things happen far more quickly than you know how to, and it can feel like getting yourself to that level will take an eternity.
This article explores some essential shortcuts and tips to help you work with Excel faster and smarter. Most of them are super simple too (I promise!).
Introduction
Although most people are used to working with Excel, they tend to always stick with what they know, which often means that shortcuts and features that can save time and enhance productivity can be overlooked.
I know it may sound insignificant, but saving a second or two here and there by learning these shortcuts WILL increase efficiency over time (and is probably what you’re seeing your colleagues do!).
Basic Excel Shortcuts
Let’s start with some fundamental shortcuts that every Excel user should know. These are the building blocks for speeding up your workflow.
Copy and Paste
Some of the most commonly known and widely used shortcuts are:
Ctrl + C for copying.
Ctrl + V for pasting.
These are used to duplicate data within a spreadsheet.
Undo and Redo
Mistakes happen to the best of us!
Ctrl + Z to undo an action.
Ctrl + Y to redo it.
Save
To quickly save your work, press Ctrl + S. This shortcut ensures your progress is never lost. I get in the habit of saving files every time I complete something I’m happy with (i.e., data input/formula/formatting, etc.).
Bonus Tip: Activate ‘Autosave’ to automatically save your files at regular intervals.
Navigation
Use Ctrl + Arrow keys to move to the edge of a data region.
Press Ctrl + Home to go to the beginning of the spreadsheet.
Use Ctrl + End to reach the last cell.
Today’s Date: Use Ctrl + ; to enter today’s date formatted as a date.
There are tons more keyboard shortcuts; it is possible to use Excel entirely without using a mouse at all!
Time-Saving Formulas
Formulas have to be Excel’s superpower; they are the starting point of automating your data. Here are some common formulas that are great to know (and you’ll probably use them in EVERY spreadsheet):
SUM
This formula adds up a range of numbers.
=SUM(A1:A10) to get the total of cells A1 to A10.
[SUB-HEADING] AVERAGE
To find the average of a range of numbers:
=AVERAGE(A1:A10)
COUNT
This formula counts the number of cells containing numbers in a specified range.
=COUNT(A1:A10)
Bonus Tip: This formula only counts cells containing numbers; use =COUNTA(A1:A10) to count ALL non-blank cells (regardless of the data type).
IF
Conditional formulas will return one result if the criteria are met, and another result if not.
=IF(A1 > 10, “Greater”, “Lesser”)
If A1 is greater than 10, the result will be ‘Greater’.
VLOOKUP
This super powerful formula allows you to search for a value in a table and return the result from a different column (on that same row).
=VLOOKUP(“John”, A1:B10, 2, FALSE)
This will find “John” in the first column and return the corresponding value from the second column.
Bonus Tip: There is a relatively new formula called XLOOKUP (circa 2019), which is a more flexible and improved version of VLOOKUP.
Advanced Excel Shortcuts
There are some more advanced shortcuts you can use to access the functionality in Excel quicker:
Data Filtering
Press Alt + down arrow to open the filter options for the current column (assuming filters are already switched on). Then press E to navigate to the Search box, enter text, and press Enter to confirm filter selection.
Auto-Fill
Select range, then press Ctrl + D to fill data down a column or Ctrl + R to fill data across a row. This is ACE for repeating patterns or formulas.
Sheet Management
Create a new sheet with Shift + F11 and navigate between sheets using Ctrl + PageUp / PageDown.
Hide/Unhide Rows and Columns: Use the keyboard to hide/unhide rows/columns.
Ctrl + 9 to hide rows.
Ctrl + 0 to hide columns.
Ctrl + Shift + 9 to unhide rows.
Ctrl + Shift + 0 to unhide columns.
Customizing Excel for Efficiency
If the above built-in features are not enough, you can also customise Excel to make it even more efficient.
Quick Access Toolbar (QAT)
Add any functions that you use a lot to the Quick Access Toolbar (at the top of the screen) for easy access, saving having to remember which ribbons each of the commands live in, or the need to navigate to them.
Right-click on any command in the ribbon and select Add to Quick Access Toolbar.
Bonus Tip: Don’t overload the QAT, as this will only make it more difficult to find the relevant command you need. Review it regularly and remove any that are not used often.
Custom Shortcuts
Excel allows you to create custom shortcuts for macros and other functions. This can be invaluable for complex tasks that require multiple steps.
Excel Time-Saving Tips
Beyond shortcuts, here are some other functions and options to save time:
Macros
Macros are scripts that will automate repetitive tasks. Record a macro to save time on any operations you perform regularly that do not require any manual input. This saves time and also increases accuracy, as it removes the potential for human error.
Templates
Create templates for frequently used spreadsheets. This eliminates the need to start from scratch each time. This can include commonly used formatting, as well as layout, formulas, and macros.
Data Validation and Conditional Formatting
Data Validation helps to maintain data integrity and consistency, ensuring invalid data cannot be entered and preventing the need for any post-data entry data cleansing.
Conditional Formatting highlights key information with formatting or icons, reducing the need for manual checks, and is automatically updated as data changes.
Integrating Excel with Other Tools
Excel can be integrated with other Microsoft Office applications and external tools for ease of presenting data:
Word and PowerPoint
Data can be copied from Excel into Word or PowerPoint to be included in reports and presentations. This can be pasted as a ‘live link’ so that data will update in Word or PowerPoint as it changes in Excel (and vice versa).
This gives the user the ability to use Excel’s functionality to calculate and generate data but then display and communicate it using the functionality of Word or PowerPoint.
Data Import/Export
Excel allows you to import data from various sources, such as CSV files, databases, or other spreadsheets and even pictures these days.
** Use Data > Get Data to access these options.
Files in Excel can also be Saved As a different file type, such as CSV or PDF.
Conclusion
Learning shortcuts and these time-saving tips in Excel can significantly enhance your efficiency and productivity and also increase the accuracy of your data. Experiment with different shortcuts and tips to find what works best for you. Even if they feel uncomfortable at first, I promise they will get easier the more you use them!
