Leave a Reply

    Your email address will not be published.

    Let Spreadsheets Do the Heavy Lifting: Number Formatting Tips

    Updated on: Jun 16th, 2015
    Data Analysis
    , , , ,
    Microsoft Excel number formatting by color.

    I bet you’ve got better things to do than fiddle with your spreadsheet for hours on end.

    And why rely on Excel’s default settings when you can easily format your spreadsheet to fit your exact preferences?

    There are numerous formatting adjustments you can make to any of the numbers in your Excel file. You can automatically add commas to large numbers, you can automatically round numbers up or down, and you can even automatically fill in cells with the color of your choice.

    Adding Commas to Large Numbers

    Does your spreadsheet have a bunch of huuuuuge numbers, like 99535767? Sometimes it’s easier to “see” these numbers when they’ve got commas, like 99,535,767 instead of 99535767. Rather than painstakingly adding those commas by hand, use Excel’s built-in Comma Style button.

    Ann K. Emery on adding commas to large numbers in Microsoft Excel

    Rounding Numbers Up or Down

    Want to round your numbers up or down? You’ve got two options.

    First, you could use the Increase Decimal or Decrease Decimal buttons. The decimal places are still there, inside your original cells, but now they’re just hidden out of view. Try clicking on one of the cells and you’ll see all those millions of decimal places hanging out in the Formula Bar.

    Your second option is the =round() function. This function has two pieces. First, tell Excel which cell contains the number that needs some rounding (e.g., A1). Second, tell Excel how many decimal places you want (e.g., 2). Now, when you use the Increase Decimal or Decrease Decimal buttons, you’ll notice that the number has been truncated. Try clicking on one of the cells and only the first two decimal places will show up in your Formula Bar.

    Ann K. Emery on automatically rounding numbers up or down in Microsoft Excel

    Color-Coding Numbers Greater Than a Certain Value

    It’s easy to color-code your numbers by hand if you’re only dealing with 5, 10, or 20 numbers. But it’s even easier to automatically color-code your numbers with Excel’s Conditional Formatting feature.

    Highlight or select the range of information that you want to color-code. On the Home tab, click on the Conditional Formatting icon. You’ll notice a couple drop-downs within drop-downs that contain dozens of color-coding features for you to explore.

    Sometimes I want to see how many numbers fell above a certain value. For example, I might want to see how many people were above age 35. Highlight or select the age values, click on the Conditional Formatting icon, select Highlight Cells Rules, and then select Greater Than.

    As shown in the pop-up window, you’ll get to customize your cut-off value (I changed my cut-off from 47 to 35 in this example). You’ll also get to customize your colors. For instance, you might want everyone older than 35 to show up as red, yellow, or green.

    GIF of Microsoft Excel’s Conditional Formatting feature.

    Color-Coding Numbers that Fall Between Two Values

    Excel’s Conditional Formatting icon has endless possibilities. In this example, I color-code everyone between the age of 25 and 45.

    On your Home tab, simply click Conditional Formatting, Highlight Cells Rules, and Between. The subsequent pop-up window gives you customization options that can be tweaked to fit your specifications.

    Ann K. Emery on automatically color-coding cells with numbers that fall between two given values using Microsoft Excel's conditional formatting features

    Color-Coding the Largest 10 Numbers

    Or, maybe you’re interested in seeing the top 10 highest ages from your list.
    Go to Conditional Formatting, Top/Bottom Rules, and Top 10 Items. You might want those highest items to appear green, red, or yellow. Or customize the colors further by selecting Custom Format from the pop-up menu.

    Ann K. Emery on automatically color-coding the top 10 values in your dataset using Microsoft Excel's conditional formatting features

    Color-Coding the Top 10% of Numbers

    Interested in making the top percentage of items stand out?

    Go to Conditional Formatting, Top/Bottom Rules, and Top 10% Items. In the pop-up window, select the exact percentage you’re interested in–10%, 25%, or 50%, etc.–and then select the colors of your choosing.

    Ann K. Emery on automatically color-coding the top 10 percent of values in your dataset using Microsoft Excel's conditional formatting features

    Checking for Duplicate Numbers

    Whenever I’m dealing with peoples’ names or ID numbers, I like to make sure each person is only listed once on my spreadsheet. Removing duplicate entries early on in the process ensures that my numbers will be accurate later on.

    It would take a lot of time – and mental energy! – to scan a long list for double-entries. No matter how hard I was paying attention, I would probably miss one or two. So, let’s let Excel do the hard work for us.

    Excel’s Conditional Formatting will change the color of duplicates to make them stand out. For example, you can add a red or yellow fill to those cells. Highlight your list of numbers and go to Conditional Formatting on the Home tab. Select the first option from the list, Highlight Cells Rules. Then, select Duplicate Values. You’ll get a new pop-up window that gives you plenty of color options. In my example, I selected a light green fill with dark green text.

    My dataset contained one set of duplicates—person 116 was accidentally listed twice. Once I spotted the error, I deleted the double-entry by highlighting one of those double rows, right-clicking, and selecting Delete.

    Ann K. Emery on using Excel's conditional formatting to check for duplicate numbers

    Undoing the Instant Color-Coding

    Want to remove that instant color-coding and get your spreadsheet back to its original state? Conditional Formatting is easy to undo.

    Go to Conditional Formatting –> Clear Rules. You can remove your conditional formatting from just a section of cells that you’ve already highlighted (Clear Rules from Selected Cells) or from everywhere within your sheet at once (Clear Rules from Entire Sheet).

    Want to learn more spreadsheet strategies? I partnered with Udemy to share more than 50 of my favorite time-saving tips. Read my full guidebook at https://www.udemy.com/tutorials/learn-excel/. 

    More about Ann K. Emery
    Ann K. Emery is a sought-after speaker who is determined to get your data out of spreadsheets and into stakeholders’ hands. Each year, she leads more than 100 workshops, webinars, and keynotes for thousands of people around the globe. Her design consultancy also overhauls graphs, publications, and slideshows with the goal of making technical information easier to understand for non-technical audiences.


      Leave a Reply

      Your email address will not be published.

      You Might Like

      Our complimentary mini course for beginners to dataviz. Takes 45 minutes to complete.


      Visualizing 24 School Divisions’ Submissions with a Dashboard in Microsoft Excel

      Amadu Sidi Bah encountered a forgotten folder on the shared drive: “Stakeholder Submissions.” These text-heavy reports had been left to gather dust. He wanted to make sure the stakeholders’ recommendations were used in policy decisions, and he decided to do something about it. In this blog post, you’ll see the Excel-based dashboard that Amadu created from those 24 reports.

      More »

      Need some time-savers in Excel? Feel like all the formulas are taking forever? Learn how to clean, analyze, and tabulate your dataset.



      Not another fluffy newsletter. Get actionable tips, videos and strategies from Ann in your inbox.