The data in your Microsoft Excel spreadsheet may not always look the way you need. In this article, we’ll offer two easy Excel hacks that help you clean up messy 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:
- Select the cell range F5:F15.
- Click on the F5 key. After selecting Special, choose Blanks and hit OK.
- 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.
