5 Hidden Excel Tricks You Never Knew Existed

Excel is arguably the best and most used spreadsheet program in history. It also provides tons of other smart tools that make it the “Swiss Army Knife” of the software world. Let’s go over some hidden Excel tricks that you never knew existed. 

Flash Fill

If you have columns that are repeating patterns, you can manually type them in or you can use Flash Fill to quickly and efficiently input the data. For example, say you’re entering names and you need just the initials for each set. Rather than manually entering all the names and then the initials, you can Flash Fill the data once you’ve established a pattern in the preceding cells.

Start with a column for the full name including a first and last name. Add a column with the title of “first initial” and one for “last initial”, then insert the initials for the first name or two on your list. From there, Excel should recognize the pattern. Once a pattern is established, you can press CTRL+E or click Data in the top menu and find Flash Fill. This will automatically fill in the “Initial” columns for subsequent rows without you having to manually add them or enter a specific formula.

Date Difference

An example of date difference is if you want to know the number of days between two specific dates. To do this, you can use the DAYS function (DAYS(start date, end date)). Entering this function in a cell and inputting the cell address of the start date and end date will give you the number of days between them. For example, the difference between 4/1 and 5/1 equals 30 days.

Hide Columns and Rows

If you want to hide the values of specific columns and rows, you can format the cells to do so. You will still see the cells, but their values will be hidden or appear empty. A great example of this is when you want to keep the content within your Excel sheet but need to keep the information private, like with payroll or accounting information.  

To do this, you can highlight the cells or columns that you want to hide, then right-click the selected area and select Format Cells from the context menu. In the format window, then select Custom format. In the Type box to the right, type three semicolons in a row (;;;) without the parenthesis. This will hide the cell values without hiding the entire column or row. Their formulas will still show if you click them, but not their values. To make the cells visible again, simply go back in and reset the previous format.

Keyboard Shortcuts

There are also some keyboard shortcuts that you can use to speed data input up without having to touch the mouse. Below are just a few of the most popular shortcuts.

• Ctrl + A – This will highlight all of the data in the spreadsheet. After the data is highlighted, you can apply formatting or perform other commands to the entire spreadsheet.
• Ctrl + B, U, I – These can be applied separately to format text inside the cells. Ctrl+B will make the text bold, Ctrl+U will underline the selected text. Ctrl+I will make the selected text italics.
• Ctrl+9, Ctrl+0 – Ctrl+9 will hide an entire row, and Ctrl+0 will hide an entire column
• Ctrl+O, Ctrl+P – Ctrl+O will bring up the Open File Dialog, and Ctrl+P will bring up the Print window for the spreadsheet.

Remove Duplicates

This function is especially useful to users who have imported data or compiled data from multiple sources. Most likely, you will encounter some sloppy data at some point that will skew your figures and statistics because of duplicates and orphaned data. Excel has a built-in function that will help you get rid of this duplicate data.

You simply highlight the cells containing the data that you want to clean and then under the Data tab on the ribbon toolbar click the Remove Duplicates button. This will analyze your data and only keep the original cell with that data in it. That way, you can obtain a unique list instead of having to drudge through all of the duplicates.

There are multiple tools and functions that can be uncovered in Microsoft Excel. One of the best ways to uncover all of this powerful functionality is to obtain Excel training through an established training company such as ONLC. ONLC provides many classes on technology, and they even offer certification courses to help further your career. Contact ONLC today to find out how you can quickly and easily become an Excel master.