Ever find yourself elbows deep in an Excel worksheet with seemingly no end in sight? You’re manually replicating columns and scribbling down long-form math on a scrap of paper, all while thinking to yourself, “There has to be a better way to do this.”
Truth be told, there probably is … you just don’t know it yet. Excel can be tricky that way. On one hand, it’s an exceptionally powerful tool for reporting and analyzing marketing data. On the other hand, without the proper training, it’s easy to feel like it’s working against you.
To help you use Excel more effectively (and save a ton of time), we’ve compiled a list of essential functions, keyboard shortcuts, and other small tricks you should know.
12 Excel Formulas, Keyboard Shortcuts & Tricks That’ll Save You Lots of Time
1) Quickly select rows, columns, or the whole spreadsheet.
Crunched for time? (Who isn’t?)
No problem. You can select your entire spreadsheet in just one click. All you have to do is simply click the tab in the top-left corner of your sheet to highlight everything all at once.
2) Automatically fill columns or rows with data.
Tired of manually entering data that follows a pattern across a bunch of cells? Excel’s Auto Fill feature is designed to minimize the work required on your end by making it easy to repeat values you’ve already input.
To do so, click and hold the lower right corner of a cell, and then drag it down or across into adjacent cells. When you release, Excel will fill in the adjacent cells with the data from the cell you first selected.
3) Quickly open, close, or create a workbook.
Need to open, close, or create a workbook on the fly?
The following keyboard shortcuts will enable you to complete any of the above actions in less than a minute’s time.
4) Customize the color of your tabs.
Have a ton of different sheets going in one workbook? Make it easier to identify where you need to go by color-coding the tabs. For example, you might label last month’s marketing reports with red, and this month’s with orange.
To do so, simply right click a tab and select “Tab Color.” A popup will appear that allows you to choose a color from an existing theme, or customize one to meet your needs.
5) Format numbers into currency.
Have raw data that you want to turn into currency? Whether it be salary numbers, marketing budget, or ticket sales for an event, the solution is simple.
Just highlight the cells you wish to reformat and select Control + Shift + $.
6) Add a comment to a cell.
Want to make a note or add a comment to a specific cell within a worksheet? Simply right-click the cell you want to comment on and then click Insert Comment. Type your comment in the text box and click outside the comment box to save it.
Cells that contain comments display a small, red triangle in the corner. To view the comment, hover over it.
7) Insert current date and time into a cell.
Whether you’re logging social media posts or keeping tabs on tasks you’re checking off your to-do list, you may want to add a date and time stamp to your worksheet. To do so, you’ll want to start by selecting the cell into which you want to insert the time, date, or time and date.
Then, depending on what you want to insert, do one of the following:
- Insert current date = Control + ; (semi-colon)
- Insert current time = Control + Shift + ; (semi-colon)
- Insert current date and time = Control + ; (semi-colon), SPACE, and then Control + Shift + ; (semi-colon).
8) Copy and duplicate formatting.
If you’ve ever spent some time formatting a sheet to your liking, you know that it’s not necessarily the most enjoyable activity. In fact, it’s pretty tedious.
For that reason, it’s likely that you wouldn’t want to have to repeat the process next time … and you don’t have to. Thanks to Excel’s Format Painter, you can easily copy the formatting from one area of a worksheet to another.
To do so, simply select the thing you’d like to replicate, then select the Format Painter option (paintbrush icon) from the dashboard, as show below:
Let’s say you have code that you want to break down into a few
10) Identify duplicate values.
In many instances, duplicate values — like duplicate content when dealing with SEO — can be troublesome if gone uncorrected. However, in other cases, you simply need to be aware of them.
Whatever the situation may be, it’s easy to surface any existing duplicate values within your worksheet in just a few quick steps. To do so, click into the Conditional Formatting option, and select Highlight Cell Rules > Duplicate Values…
different segments. Rather than manually retyping each piece of the code into its respective column, users can leverage a series of string functions to deconstruct the sequence as needed.
- Purpose: Used to extract the first X numbers or characters in a cell.
- Syntax: =LEFT(text, number_of_characters)
- Text. The string that you wish to extract from.
- Number_of_characters. The number of characters that you wish to extract starting from the left-most character.
11) Add up the sum of cells that meet a certain criteria.
Let’s say you want to determine the profit you generated from a list of leads who are associated with specific area codes or calculate the sum of group of employees’ salaries that fall above the a particular amount. Sounds a little time-consuming, doesn’t it?
With the SUMIF function, it doesn’t have to be.
- Purpose: Used to add up cells that meet a certain criteria.
- Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- Sum_range. The range of cells you’re going to add up.
- Criteria_range1. The range that is being tested using Criteria1.
- Criteria1. The criteria that determine which cells in Criteria_range1 will be added together.
To see the function in action, check out this example:
12) Clean up irregular spacing.
Ever have one of your colleagues send you a worksheet with some funky spacing going on? Not only do these rogue spaces can make if difficult to search for data, but it also affects the results when you try to add up columns of numbers.
Rather than painstakingly removing and adding spaces as needed, you can clean up any unnecessary spacing using the TRIM function.
- Purpose: Used to remove extra spaces from data (except for single spaces between words).
- Syntax: =TRIM(“Text”)
- Text. The text from which you want to remove spaces.
Here’s an example of how we used the TRIM function to remove extra spaces after a name on our list: