Microsoft Excel has become an essential staple for workplace professionals across every industry, especially when quickly working with data and performing basic analyses. With hundreds of functions, it can be overwhelming to try to learn them all as well as know which are most effective. But if used correctly, these functions can help save you an immense amount of time and headache. Let’s explore a few classic Excel tricks every analyst should have in their toolbox.
1. Heatmap
It’s easy to get lost in the hundreds of rows and columns that make up most spreadsheets. You might be left asking yourself, what do all of these numbers mean? This is where data visualizations are key in helping you understand your data and generate insights quickly.
One effective way to do this is with the use of color and Excel’s heatmap function. To put it simply, heat maps are a visual representation of your data through the use of color. These charts are perfect for comprehensive overviews of your data as well as additional analysis.
This trick can be broken down into three simple steps:
- Select the cells and values you want to include.
- Under the Home tab, click on Conditional Formatting.
- Select Color Scales from the drop-down menu and choose your desired color scale selection.
Following these steps, you can now see your data highlighted in a gradient-based on its value. This can visually assist, for example, in identifying critical dips in sales or inventory by highlighting those cells as red. Overall, heat maps are extremely versatile and can be used to understand data intuitively. They also make for a great visual stimulus in any dashboard or report!
2. Remove Duplicates
The last thing you want in your data is duplicate entries or values. This poses an issue of inaccuracy and other inherent risks in your analysis. Though, removing these duplicates is quite simple when using Excel, here are the steps to follow for one method.
To begin, we need to first identify if there are any duplicates present in your spreadsheet. We can do this by highlighting any duplicates through Excel’s Conditional Formatting function.
- Under the Home tab, click on Conditional Formatting.
- Select Highlight Cells Rules from the drop-down menu, then select Duplicate Values.
- Determine your formatting preferences and click OK.
Any duplicates present in your data will be highlighted based on the color preferences you determined earlier. Now that you’ve detected the duplicates, you can easily remove them by going to the Data tab and clicking Remove Duplicates. Excel will then tell you how many duplicates were detected and the total removed from your sheet. Duplicate free in only a few simple clicks! This trick can help you minimize discrepancies as well as save time trying to manually detect and delete duplicate values.
3. Filling All Empty Cells
Chances are your dataset contains a few empty cells, this could be due to incomplete data or any number of reasons. In order to avoid any issues when it comes to your analysis or when creating models, it’s important to fill these cells ahead of time.
Follow these steps to identify and fill all empty cells at once:
- Under the Home tab, click Find & Select.
- Select Go To Special from the drop-down menu and select Blanks from the provided menu options.
- Fill an empty cell with your desired value or text (e.g. N/A) and press CTRL + ENTER.
With this function, all of your empty cells can be identified and filled in a matter of seconds. This trick will help you save time ciphering through columns trying to manually detect and fill empty cells. Additionally, this can be especially helpful when working with large data sets used for creating models.
