One of the beauties of using Excel is that it’s a constant learning curve. Even for the most seasoned of pros, there are always more hints and tips that we can pick up along the way to boost our spreadsheet expertise. Here are some of my favorites.

1 Hide Data but Keep Cells

It’s surprising that Excel doesn’t have a direct command for hiding data in visible cells, as this functionality has many uses.

In this Excel heat map, which shows the position from which goals have been scored, the colored cells do all the talking, so I don’t need to display the corresponding numbers.

Similarly, the scores in this table are derived from a calculation that doesn’t hold any numerical value—other than for comparative purposes—and the data bars in this table give me a clear picture of how the employees compare. As a result, there’s no requirement for the overall scores to be visible.

When I print this information sheet, I want the employee’s salary to be hidden, as it’s sensitive information.

To keep those cells but hide their data, after selecting them, click the “Number Format” dialog box launcher in the corner of the Number group in the Home tab.

Then, click “Custom” in the left-hand menu, and type ;;; (three semicolons) into the Type field.

When you click “OK,” the numbers in the selected cells will disappear, even though the data is still there.

2 Change Numerical Data to Representative Text

There are many ways to generate text based on data, including the XLOOKUP function, conditional formatting, and the SWITCH function. However, many of these techniques require separate columns for the data and their representative text.

In column D of this data sheet, I want Excel to turn any positive numbers to “PROFIT,” any negative numbers to “LOSS,” and any zeros to “BREAK EVEN.”

If you want to do the same, select and right-click the relevant cells, and click the “Number Format” dialog box launcher in the Number group in the Home tab.

Now, after clicking “Custom,” type “PROFIT”;”LOSS”;”BREAK EVEN” into the Type field, making sure you use double quotation marks around each phrase and a semicolon to separate them. It’s also important you type the phrases in this order—the first is for positive numbers, the second is for negative numbers, and the third is for zero.

When you click “OK,” the numbers change to the words you defined in the previous step, making it easy for you to assess your data in an instant.

3 Cutting, Copying, Replacing, and Inserting Cells, Rows, and Columns

For too long, I used several steps to move, copy, and insert cells, rows, and columns around my spreadsheet. When I realized I could use my mouse while pressing one or two keys on my keyboard, this significantly sped up my data organization.

Replace: Click and Drag

To replace the contents of a cell, row, or column with another, simply select, click, and drag it to where you want to be. Then, click “OK” when Excel reminds you that you’re about to replace some data.

Copy and Replace: Ctrl, Click, and Drag

To duplicate the contents of a cell, row, or column where data is already entered, select the relevant cell, row, or column, hold Ctrl, and drag it to its copied location. Strangely, when you perform this action, Excel doesn’t remind you that you’re about to replace some data, so make sure this is really what you want to do before you do it!

Cut and Insert: Shift, Click, and Drag

If you want to squeeze the contents of a cell, row, or column between existing data without replacing it, hold Shift as you click and drag it.

Copy and Insert: Shift+Ctrl, Click, and Drag

Finally, to create a copy of a cell, row, or column without losing any data in the process, hold Shift and Ctrl as you click and drag the data.

4 Repeat Your Last Action

Maybe you’re familiar with the Format Painter tool, which duplicates formatting from one area of Excel to another. Also, if you double-click the Format Painter icon, you can apply the formatting to many separate areas of your spreadsheet.

A similar (but, importantly, slightly different) way to achieve duplication is the F4 key. Where Format Painter copies all the formatting from the selected cell or cells, F4 repeats the last action, including pasting, inserting and deleting rows, adding formatting to cells, and adding new worksheets.

In this example, my team manager asked me to insert a new column between each existing column so that she could add some analytical comments. First, I will right-click the header for column C to select the whole column, and click “Insert” to add a column between columns B and C.

Now, I will select column E.

When I press F4, a new column will be added between columns C and D, and I can then do the same with the remaining columns.

This saves me from having to click Insert each time and helps me avoid the error-prone method of selecting all the relevant columns first. Remember, this tip can be used to repeat any action in Excel, so go ahead and give it a try!

5 Insert, Check, and Uncheck Many Checkboxes Simultaneously

Excel’s checkbox tool, added to the program in June 2024, is great for creating checklists, tracking task progress, and visualizing data in an instant.

To add a checkbox, click “Checkbox” in the Insert tab on the ribbon. If you don’t see the checkbox option, customize your ribbon or use Excel’s search box at the top of the window.

To add several checkboxes to several cells at the same time, select the relevant cells first, and then click “Checkbox” in the Insert tab.

While you can use your mouse to check and uncheck these checkboxes, this would take forever if you wanted to check or uncheck lots of text boxes at the same time. Instead, select all the cells containing the checkboxes you want to check or uncheck, and press Spacebar.

6 Add the Current (Fixed) Date and Time

Excel’s date and time functions—like NOW and TODAY—are great for returning a time and date that update whenever you perform an action in Excel (also known as volatile functions).

However, sometimes, you might prefer to add the current date and time as a fixed timestamp, like when you’re marking the time at which a task has been completed. You can do this using straightforward keyboard shortcuts:

• Press Ctrl+; to insert the current date.
• Press Ctrl+Shift+; to insert the current time.
• Press Ctrl+; > Space > Ctrl+Shift+; to insert the current date and time together in the same cell.

7 Duplicate the Excel Window

If you’re working on a large worksheet, it can be handy to view different areas at the same time. Similarly, you might find it useful to see two tabs at once, saving you from clicking back and forth between them. This is where Excel’s New Window tool—accessed via the View tab on the ribbon—comes in handy.

When you click this icon, an exact duplicate of your workbook opens in a new window. Any changes you make to one copy will automatically update in the other, and you can close either the original or the duplicate when you’re ready to return to a single-window view.

Learning new Excel tips and tricks is one way to optimize your efficiency and workflow. However, another way to become an Excel pro is to know the many traps people often fall into when using the program, meaning you can avoid the same issues as you work.