3 Useful Excel Tricks You Wish You Knew Yesterday

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.

Heatmap

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:

  1. Select the cells and values you want to include.
  2. Under the Home tab, click on Conditional Formatting.
  3. 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!

Remove Duplicates

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.

  1. Under the Home tab, click on Conditional Formatting.
  2. Select Highlight Cells Rules from the drop-down menu, then select Duplicate Values.
  3. 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.

Filling All Empty Cells

Follow these steps to identify and fill all empty cells at once:

  1. Under the Home tab, click Find & Select.
  2. Select Go To Special from the drop-down menu and select Blanks from the provided menu options.
  3. 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.

Click here to read more content!

Marketing Coordinator at Inzata Analytics | AI for Big Data