Excel hacks: clean up messy spreadsheet data

Filling incomplete columns

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!

Organize data with the dynamic array functions

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

The 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.

The 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.

Marie Kondo would be proud of your Excel organizational skills! For more Excel tips and tricks, take our Microsoft Excel Fundamentals + Data Analytics Certificate program. This program will 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.