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.
Download our FREE ebook Excel automation for accountants
With step-by-step tutorials and real world examples, learn valuable automation functions in Excel that save time, improve accuracy, and and enhance your skills!
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:
- 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!
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.
Learn more with Excel CPE
Excel offers a wide variety of functions, formulas, and automation that can save you hours of work while improving accuracy and outcomes. To help you make the most out of this software, we offer a wide variety of CPE courses to support your learning!
Check out Becker's wide range of CPE courses that teach you to make the most of this powerful tool:
- Excel: Technical Analysis Trading Strategies
- Excel: Enterprise Risk Management
- Excel: Magic with Excel
- Excel: Solve Hard Problems in Corporate Finance
- Excel Metrics: Best Practices
- Python for Excel Users: A Gentle Introduction
These and many more Excel-focused, CPE credit-earning courses are included in Becker's Prime CPE subscription. Sign up now for 12 months of access to over 1700 on-demand, webcast, and podcast CPE courses!
Unlock unlimited CPE with a Prime Subscription
Becker makes it easy to meet your CPE requirements, gain new skills, and stay aware of critical updates and changes in the industry!
With Prime, you can access over 1,700 courses for a full year and earn unlimited CPE credits.
Related Articles
Share
