Excel hacks: How to clean up messy spreadsheet data

person vacuuming carpet

The data in your Microsoft Excel spreadsheet may not always look the way you need, and a messy Excel spreadsheet will slow you down and cause frustration. Mastering the program's many shortcuts is one way to make your job easier and your work more efficient. In this article, we’ll offer two easy Excel hacks that walk you through how to clean up data in Excel. 

How to clean up data in Excel hack #1: Filling incomplete columns

One common problem that Excel-users face is incomplete columns. Some data has been filled in, but you need to repeatedly type the same title to fill out the rest. But you can skip the roadblock and clean up your messy Excel data with this trick. 

In the worksheet Fill Down, pictured in Figure 1, we list sales categories for four stores. We want to fill in the correct store in each row where a store is not listed.

Figure 1: Stores are missing

To easily fill in the blank rows in column F with the correct store, complete the following steps:

  1. Select the cell range F5:F15.
  2. Click on the F5 key. After selecting Special, choose Blanks and hit OK.
  3. In the Formula Bar type =F4 and then invoke the Ctrl+Enter keystroke combination. Amazingly your worksheet now looks like Figure 2.

Figure 2: Store names are filled in!

How to clean up data in Excel hack #2: Organize data with the dynamic array functions

An easy way to clean up data in Excel is by learning Office 365's amazing dynamic array functions. These formulas sort, filter, extract, and organize values to help you quickly analyze data and extract new insights. 

In the cell range D3:H79 we list five-man player combinations that the Dallas Mavericks played in their victorious playoff series against the Utah Jazz. Many combinations were played more than once. We want to find a list of all Maverick players who played in the series, as well as each unique lineup that played in the series, using the Office 365 TOCOL and UNIQUE functions. The result is shown in Figure 3.

Figure 3: Maverick Player and Lineup Combinations

Excel TOCOL function

The TOCOL function will put entries from multiple columns in a single column. You only need to enter this formula in a single cell, and it will populate multiple cells. This is an example of Office 365’s amazing dynamic array functions (see my book Data Analysis and Business Modeling for more information on dynamic array functions). Upon entering the formula =TOCOL(D3:H79)  in cell L3 we see all the lineup data in a single column.

Excel UNIQUE function

Next, we want to extract from column L a list of unique player names. This is simple when we use the UNIQUE dynamic array function. Simply enter the formula =UNIQUE(L3:L387) in cell O3 and you now have list of all Maverick players.

Note that when we enter a dynamic array formula in a single cell, then all cells “filled” by the formula show the formula in light gray. Also, if you do not leave enough blank space for a dynamic array formula to fill in results, you will receive a #SPILL error. To resolve this problem, delete the data that prevents your formulas from completing their work.

To find a list of unique Maverick lineups, enter in cell S3 the formula =UNIQUE(D3:H79). This formula worked across five columns and returned all (48) unique lineup combinations.

Elevate your Excel skills with Becker CPE

Now that you know how to clean up data in Excel, you'll streamline your workflow and save yourself a lot of headaches. Marie Kondo would be proud of your organizational skills. 

You can learn more Excel skills with Becker CPE courses! Check out our Microsoft Excel Fundamentals + Data Analytics Certificate program. This program includes 14 CPE courses to strengthen your Excel skills, helping to make you an expert in a program that’s essential for accountants.  

Now Leaving Becker.com

You are leaving the Becker.com website. Once you click “continue,” you will be brought to a third-party website. Please be aware, the privacy policy may differ on the third-party website. Adtalem Global Education is not responsible for the security, contents and accuracy of any information provided on the third-party website. Note that the website may still be a third-party website even the format is similar to the Becker.com website.