Siegel, Here Are Your Articles for Monday, April 04, 2022
Is this email not displaying correctly?
View it in your browser .
Our Website Contact Us About Us Services Tutorials Acumatica
Share Save

Turbocharge Your Spreadsheets with Heat Map Formatting

 

Heat maps are a common data visualization technique that uses color to offer a sense of scale. In this article you'll see how Excel's Conditional Formatting feature can be used to create an instant heat map for showing a sense of scale within a column of numbers. The technique is simple, but there is a nuance you'll need to be aware of.

The steps involved in building a heat map are shown in the figure:

  1. Click any single cell within the list. Users sometimes mistakenly select a column before sorting. Doing so can cause Excel to sort the column separate from the rest of the data in the list.
  2. Activate Excel's Data menu.
  3. Click the Sort Z-A command to sort the numbers from highest to lowest.
  4. Select the numbers you wish to format. It's important to wait and select the numbers after you sort, otherwise the sort may not work correctly.
  5. Choose Conditional Formatting from Excel's Home menu.
  6. Choose Color Scales from within the Conditional Formatting menu.
  7. Choose a color scheme. Excel offers a live preview as you hover over each scheme, so you'll be able to see the impact before you commit to it.
  8. If you've sorted the numbers in descending order, you'll have a heat map. Conditional Formatting will still work if you didn't sort the numbers, but you'll have a scattered array of colors as opposed to a smooth transition.

The Color Scales Conditional Format requires numbers, and unfortunately cannot be applied to entire rows if some columns contain text. It is possible, however, to manually create conditional formatting rules for such instances.

If you change your mind about applying conditional formatting, either click Undo immediately after you apply the formatting, or:

  1. Choose Conditional Formatting from Excel's Home menu.
  2. Choose Clear Rules.
  3. Choose between Clear Rules from Selected Cells and Clear Rules from Entire Sheet.

 

 
Share Save

Your Comments

Siegel Solutions Inc
Siegel Solutions Inc
(781) 487-7000
info@siegelsolutions.com
144 Gould Street Suite 205
Needham, MA 02494
Friend Me on Facebook
Follow Me on Twitter
Connect with me on LinkedIn
Saved Articles
Comments and Feedback
Refer A Friend
Your Privacy
Our firm provides the information in this e-newsletter for general guidance only, and does not constitute the provision of legal advice, tax advice, accounting services, investment advice, or professional consulting of any kind. The information provided herein should not be used as a substitute for consultation with professional tax, accounting, legal, or other competent advisers. Before making any decision or taking any action, you should consult a professional adviser who has been provided with all pertinent facts relevant to your particular situation. Tax articles in this e-newsletter are not intended to be used, and cannot be used by any taxpayer, for the purpose of avoiding accuracy-related penalties that may be imposed on the taxpayer. The information is provided "as is," with no assurance or guarantee of completeness, accuracy, or timeliness of the information, and without warranty of any kind, express or implied, including but not limited to warranties of performance, merchantability, and fitness for a particular purpose.
Powered by
Copyright © IndustryNewsletters All rights reserved.

This email was sent to: jeff@siegelsolutions.com

Mailing address: 144 Gould Street Suite 205, Needham, MA 02494