{"id":3509,"date":"2019-05-01T11:20:31","date_gmt":"2019-05-01T17:20:31","guid":{"rendered":"https:\/\/www.vertex42.com\/blog\/?p=3509"},"modified":"2022-01-10T22:25:23","modified_gmt":"2022-01-11T04:25:23","slug":"how-to-use-conditional-formatting-in-excel","status":"publish","type":"post","link":"https:\/\/www.vertex42.com\/blog\/excel-tips\/how-to-use-conditional-formatting-in-excel.html","title":{"rendered":"How to Use Conditional Formatting in Excel"},"content":{"rendered":"<div class=\"feature-image\" itemprop=\"image\" itemscope=\"\" itemtype=\"https:\/\/schema.org\/ImageObject\">\n<img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/feature\/how-to-use-conditional-formatting-730x382.png\" alt=\"How to Use Conditional Formatting in Excel\" style=\"max-width:100%;margin:1em auto;\"><meta itemprop=\"url\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/feature\/how-to-use-conditional-formatting-730x382.png\"><meta itemprop=\"width\" content=\"730\"><meta itemprop=\"height\" content=\"382\"><\/div>\n<p>Conditional formatting is one of my favorite features in both Excel and Google Sheets. I love the way it can make a boring hard-to-interpret spreadsheet more interesting and easier to use.<\/p>\n<p>Conditional formatting is mostly used for data analysis, such as adding color scales, highlighting high and low values, identifying duplicates, and marking outliers. Conditional formatting can be also used for graphical interface elements such as progress bars, graying out completed tasks, changing number formats, or displaying a gantt chart.<\/p>\n<p>In this article, I'll show over 20 different ways that you can use conditional formatting, starting with some basics and presenting some advanced techniques as well. Many of the examples come from templates that you can download and experiment with.<\/p>\n<p class=\"downloadlink\"><span class=\"icon16 excel\"><\/span><a href=\"\/Files\/examples\/ConditionalFormatting.xlsx\" onClick=\"ga('send','event', 'Downloads', 'Examples', 'ConditionalFormatting.xlsx');\" rel=\"nofollow\"><b>Download the Example File<\/b><\/a> (ConditionalFormatting.xlsx)<\/p>\n<div class=\"contents\">\n<p>This Article (bookmarks):<\/p>\n<ul>\n<li><a href=\"#what-is-conditional-formatting\">What is Conditional Formatting?<\/a>\n<ul>\n<li><a href=\"#highlight-duplicates\">How to Highlight Duplicates<\/a><\/li>\n<li><a href=\"#color-scales\">How to add Color Scales<\/a><\/li>\n<li><a href=\"#data-bars\">How to add Data Bars in Excel<\/a><\/li>\n<li><a href=\"#overdue-dates\">Highlight Overdue Dates<\/a><\/li>\n<li><a href=\"#icon-sets\">How to add Icon Sets in Excel<\/a><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#view-edit-rules\">How to View and Edit Conditional Formatting Rules<\/a><\/li>\n<li><a href=\"#rule-order\">Rule Order Matters<\/a><\/li>\n<li><a href=\"#progress-bar\">How to create an In-Cell Progress Bar<\/a><\/li>\n<li><a href=\"#highlight-every-other-row\">How to Highlight Every Other Row<\/a><\/li>\n<li><a href=\"#based-on-another-cell\">Conditional Formatting Based on Another Cell<\/a><\/li>\n<li><a href=\"#formula-rule\">How to create a Formula Rule<\/a>\n<ul>\n<li><a href=\"#highlight-an-entire-row\">How to Highlight an Entire Row<\/a><\/li>\n<li><a href=\"#highlight-based-on-a-range\">How to Highlight Based on a Range<\/a><\/li>\n<li><a href=\"#highlight-values-not-in-a-range\">How to Highlight Values NOT in a Range<\/a><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#create-a-gantt-chart\">Create a Gantt Chart<\/a><\/li>\n<li><a href=\"#data-bars-two-groups\">Use Data Bars to Compare Two Groups<\/a><\/li>\n<li><a href=\"#link-min-max-to-cells\">Control Scaling by Linking Min\/Max to Cells<\/a><\/li>\n<li><a href=\"#custom-number-formats\">Change Number Formatting with CF Rules<\/a>\n<ul>\n<li><a href=\"#change-date-formats\">Change Date Formats to d\/m\/yyyy<\/a><\/li>\n<li><a href=\"#hide-zero-values\">Hide Zero Values<\/a><\/li>\n<li><a href=\"#automatic-indenting\">Automatic Indenting<\/a><\/li>\n<li><a href=\"#thousands-millions\">Display 23K, 23M, 23B Based on Value<\/a><\/li>\n<li><a href=\"#custom-icons\">Display Custom Icons<\/a><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#copy-conditional-formatting\">How to Copy Conditional Formatting<\/a><\/li>\n<li><a href=\"#volatile\">Conditional Formatting is Volatile<\/a><\/li>\n<\/ul>\n<\/div>\n<h2 id=\"what-is-conditional-formatting\">What is Conditional Formatting?<\/h2>\n<div class=\"alignright\">\n<img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/how-to-apply-conditional-formatting-in-excel.png\" alt=\"How to Apply Conditional Formatting in Excel\" width=\"199\" height=\"386\" class=\"alignright\" \/>\n<\/div>\n<p>Conditional Formatting in a spreadsheet allows you to change the format of a cell (font color, background color, border, etc.) based on the value in a cell or range of cells, or based on whether a formula rule returns TRUE.<\/p>\n<p><span style=\"color:#2b5482;\"><b>To Apply Conditional Formatting in Excel<\/b><\/span>: First, select the cells you want to format. Then, go to <b>Home &gt; Conditional Formatting<\/b> and select an option from the built in menu, or click on <b>Manage Rules<\/b>.<\/p>\n<p><span style=\"color:#2b5482;\"><b>To Apply Conditional Formatting in Google Sheets<\/b><\/span>: First, select the cells you want to format. Then, go to <b>Format &gt; Conditional Formatting<\/b> and click on \"Add another rule\" in the side panel.<\/p>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTE<\/span> <span style=\"color:darkred;\"><b>Google Sheets<\/b><\/span> currently provides fewer conditional formatting options and controls than Excel (for example, no data bars or icon sets). The focus of this article will be on how to use conditional formatting in Excel, but many of the techniques will apply just as readily to Google Sheets.<\/p>\n<\/div>\n<p>Here are a few things you can do with the built-in options in Excel:<\/p>\n<div class=\"clear\"><\/div>\n<h3 id=\"highlight-duplicates\">1. How to Highlight Duplicates<\/h3>\n<div class=\"alignright\">\n<img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/cf-example-highlight-duplicates.png\" alt=\"Conditional Formatting Example: Highlight Duplicates\" width=\"187\" height=\"171\" class=\"aligncenter imgshadow\">\n<\/div>\n<p>Select your range of cells, then go to <b>Home &gt; Conditional Formatting &gt; Highlight Cells Rules &gt; Duplicates<\/b>.<\/p>\n<p>After viewing the duplicates, you can decide whether you need to remove them manually or use the built-in tool via Data &gt; Data Tools &gt; Remove Duplicates.<\/p>\n<p>Highlighting duplicates in Google Sheets requires using a custom formula rule such as <span class=\"formula-in-text\">=COUNTIF(A:A,A1)&gt;1<\/span> to highlight the duplicates in column A.<\/p>\n<div class=\"clear\"><\/div>\n<h3 id=\"color-scales\">2. How to add Color Scales<\/h3>\n<div class=\"alignright\">\n<img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/cf-example-color-scale.png\" alt=\"Conditional Formatting Example: Color Scales\" width=\"187\" height=\"196\" class=\"imgshadow\">\n<\/div>\n<p>Select your range of cells, then go to <b>Home &gt; Conditional Formatting &gt; Color Scales<\/b> and pick the color range that makes sense (usually, green=good and red=bad).<\/p>\n<p>Color Scales are very useful for seeing high and low values within a large data set. Color Scales can also be used to create heat maps, like this <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/calendar-chart.html\">Calendar Heat Map Chart<\/a>:<\/p>\n<div class=\"clear\"><\/div>\n<p><a href=\"\/ExcelTemplates\/calendar-chart.html\"><img decoding=\"async\" src=\"\/\/cdn.vertex42.com\/ExcelTemplates\/Images\/calendar-heat-map-template.png\" alt=\"Calendar Heat Map Chart\" class=\"aligncenter\"><\/a><\/p>\n<div class=\"clear\"><\/div>\n<h3 id=\"data-bars\">3. How to add Data Bars in Excel<\/h3>\n<div class=\"alignright\">\n<img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/cf-example-data-bars.png\" alt=\"Conditional Formatting Example: Data Bars\" width=\"187\" height=\"172\" class=\"imgshadow\">\n<\/div>\n<p>Select your range of cells, then go to <b>Home &gt; Conditional Formatting &gt; Data Bars<\/b> and select the style you want.<\/p>\n<p>Data Bars let you create horizontal bar charts and progress bars directly within a group of cells. In the example on the right, I'm using data bars to show a histogram of class grades.<\/p>\n<p>The default settings for Data Bars scale the bars automatically based on the values in the Applies To range. You can control the scale used for the bars by editing the rule's settings. I'll be showing a couple examples of that later in this article.<\/p>\n<div class=\"clear\"><\/div>\n<h3 id=\"overdue-dates\">4. How to Highlight Overdue Dates<\/h3>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/conditionally-format-overdue-dates-calibration-log-example.png\" alt=\"Conditionally Format Overdue Dates - Calibration Log Example\" width=\"426\" height=\"107\" class=\"aligncenter imgshadow\"><\/p>\n<p>To highlight overdue dates:<\/p>\n<ol>\n<li>Select the range of dates and go to Home &gt; Conditional Formatting &gt; Highlight Cells Rules &gt; Less Than...<\/li>\n<li>Enter <span class=\"formula-in-text\">=TODAY()+30<\/span> to highlight dates earlier than 30 days from now. Enter <span class=\"formula-in-text\">=TODAY()<\/span> to highlight dates earlier than today.<\/li>\n<li>Choose a format option from the drop-down box, then click on OK.<\/li>\n<\/ol>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/conditional-formatting-to-highlight-overdue-dates.png\" alt=\"Conditional Formatting to Highlight Overdue Dates\" width=\"471\" height=\"133\" class=\"aligncenter imgshadow\"><\/p>\n<p>Examples of this technique can be found in the <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/equipment-calibration-log.html\">Equipment Calibration Log<\/a> and <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/to-do-list-template.html\">To Do List<\/a> templates.<\/p>\n<div class=\"clear\"><\/div>\n<h3 id=\"icon-sets\">5. How to Add Icon Sets in Excel<\/h3>\n<div class=\"alignright\">\n<img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/cf-example-icon-sets.png\" alt=\"Conditional Formatting Example: Icon Sets\" width=\"187\" height=\"196\" class=\"imgshadow\">\n<\/div>\n<p>To add Icon Sets, select your data and then go to <b>Home &gt; Conditional Formatting &gt; Icon Sets<\/b> &gt; and choose one of the options. You will amost always have to customize the settings for Icon Sets.<\/p>\n<p>This example, based on the <a href=\"\/ExcelTemplates\/excel-checkbook.html\">Checkbook Register Template<\/a>, uses a green circle icon (<span style=\"color:#68a490;\">&#11044;<\/span>) to show when an account balance is &gt;=$500, yellow\/orange (<span style=\"color:#eac282;\">&#11044;<\/span>) when it is less than $500, and red (<span style=\"color:#d65532;\">&#11044;<\/span>) when the balance is negative.<\/p>\n<p>After selecting the icon set from the built-in menu, you need to edit the rule to define these different values. To do that, select one of the cells in the Balance column, go to <b>Home &gt; Conditional Formatting &gt; Rules Manager<\/b> and click on <b>Edit Rule<\/b>. The image below shows the settings used in this example:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/icon-sets-account-balance-example.png\" alt=\"Icon Sets - Account Balance Example Settings\" width=\"544\" height=\"266\" class=\"aligncenter border imgshadow\"><\/p>\n<p><b>TIP:<\/b> One of the best ways to learn how Icon Sets work is to play with all of the different drop-down options in the rule settings.<\/p>\n<p>For examples of other Icon Sets, check out the <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/feature-comparison.html\">Feature Comparison Template<\/a>:<\/p>\n<p><a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/feature-comparison.html\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/ExcelTemplates\/Images\/feature-comparison-table-template.png\" alt=\"Feature Comparison Template - Examples of Icon Sets\" style=\"width:100%;\" class=\"aligncenter border imgshadow\"><\/a><\/p>\n<div class=\"clear\"><\/div>\n<h2 id=\"view-edit-rules\">How to View\/Edit Conditional Formatting Rules<\/h2>\n<p>Vertex42 has many templates that use both simple and advanced conditional formatting techniques. If you are using a template and want to figure out how the conditional formatting works, or want to delete or change rules, you will need to know a couple of things:<\/p>\n<p>1) To view the rules for <i>selected cells<\/i>, go to <b>Home &gt; Conditional Formatting &gt; Rules Manager<\/b><\/p>\n<p>2) To <i>view ALL the rules<\/i> in the entire worksheet, select \"This Worksheet\" from the drop-down at the top of the Rules Manager window.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/conditional-formatting-rules-manager-task-checklist-example.png\" alt=\"Conditional Formatting Rules Manager\" width=\"628\" height=\"300\" class=\"aligncenter imgshadow\"><\/p>\n<p>The image above shows the 5 rules used in the <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/task-list-template.html#TaskChecklist\" target=\"_blank\" rel=\"noopener noreferrer\">Task Checklist Template<\/a>. The first rule changes tasks to <span style=\"text-decoration:line-through;color:#999;\">gray strike-through<\/span> when the Done column has a check mark. The next 3 rules highlight specific text in the Priority column. The last rule adds a Progress Bar in the % Complete column.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/conditional-formatting-task-checklist-example.png\" alt=\"Conditional Formatting - Task Checklist Example\" width=\"585\" height=\"276\" class=\"aligncenter imgshadow\"><\/p>\n<p>I will be using this template to demonstrate some techniques and key facts about conditional formatting, so I would recommend that you download it and experiment with it as you continue to read.<\/p>\n<div class=\"clear\"><\/div>\n<h2 id=\"rule-order\">Rule Order Matters<\/h2>\n<p>The Task Checklist Template mentioned above demonstrates two key points about rule order or heirarchy.<\/p>\n<div class=\"pull-quote\">\n<b>Key Points<\/b><\/p>\n<ol style=\"margin-top:0.25em;\">\n<li>Conditional formatting rules are evaluated one at a time starting with the rule listed at the top.<\/li>\n<li>A later rule cannot override the formatting already modified by a preceding rule.<\/li>\n<\/ol>\n<\/div>\n<p>Here is the list of rules again:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/conditional-formatting-rules-manager-task-checklist-example.png\" alt=\"The 5 Rules Used in the Task Checklist Template\" width=\"628\" height=\"300\" class=\"aligncenter imgshadow\"><\/p>\n<p>Key Point #2 means that if the first rule has already changed the font color to gray, the following rules cannot change the font color to green, yellow, or red. It's first come, first serve.<\/p>\n<p>In Excel, the font color and fill color (and border, and font style, and ...) can be affected <b>independently<\/b> by different rules. This means that you can have one rule that changes a font to gray, a different rule that changes the cell color to red, and a different rule that adds a data bar.<\/p>\n<h3 id=\"stop-if-true\">Why do I need to check the Stop-If-True box?<\/h3>\n<p>With the <b>Stop-If-True<\/b> box checked, none of the following rules will be evaluated if the condition of that first rule is met.<\/p>\n<p>That is why the first task row doesn't show a Data Bar in the %Complete column and why the Priority \"HIGH\" is not highlighted red.<\/p>\n<p><b>What would happen if I didn't check the Stop-If-True box?<\/b> Go ahead and try it ... or look at the next image.<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/conditional-format-example-stop-if-true-not-checked.png\" alt=\"Conditional Format Example, Stop-if-True Not Checked\" width=\"542\" height=\"143\" class=\"aligncenter imgshadow\" style=\"margin-bottom:0;\"><span class=\"clear\"><i>Result when Stop-if-True is <b>Not<\/b> Checked<\/i><\/span><\/p>\n<p>Notice how the red fill color is applied to the <span style=\"border:1px solid #bbb;background-color:#f8c9d1;color:#888;text-decoration:line-through;padding:3px 5px;\">HIGH<\/span> cell. The first rule does not define a fill color, but it does change the font color. That means that the 4th rule can change the fill color to light red, but it can't override the font color.<\/p>\n<p><b>Excel vs. Google Sheets<\/b>: In Excel, the Number Format, Font Color, Font Style, Font Underline, Font Effects, Fill Color, Fill Effects, Border Color, Border Style, Data Bars, and Icon Sets can be affected <b>independently<\/b> by different rules. That isn't the case in Google Sheets. In Google Sheets, conditional formatting behaves as though all rules are Stop-If-True. Excel is much more flexible and powerful in that respect.<\/p>\n<div class=\"clear\"><\/div>\n<h2 id=\"progress-bar\">How to Create an In-Cell Progress Bar<\/h2>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/progress-bar-using-conditional-formatting.png\" alt=\"Create a Progress Bar Using Conditional Formatting\" width=\"386\" height=\"111\" class=\"aligncenter imgshadow\"><\/p>\n<p>You've already seen how to add a Data Bar, so using a Data Bar for showing progress based on a percentage is as simple as making a few changes to the Data Bar settings.<\/p>\n<p>To see how it works in the <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/task-list-template.html#TaskChecklist\" target=\"_blank\" rel=\"noopener noreferrer\">Task Checklist<\/a> template, go to <b>Home<\/b> &gt; <b>Conditional Formatting<\/b> &gt; <b>Manage Rules<\/b>, click on the Data Bar rule, then click on the <b>Edit Rule<\/b> button.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/cf-settings-for-progress-bar-in-excel.png\" alt=\"Conditional Format Settings for a Progress Bar in Excel\" class=\"aligncenter imgshadow\"><\/p>\n<p>The progress should be a value between 0% and 100%. When you create a progress bar, you need to change the Minimum value to Type:Number &amp; Value:0, and change the Maximum to Type:Number &amp; Value:1.<\/p>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTE<\/span> <span style=\"color:darkred;\"><b>Google Sheets<\/b><\/span> currently doesn't provide an in-cell data bar option. But, you can mimic the effect using the formula <span class=\"formula-in-text\">=REPT(\"\u2588\",ROUND(<i>percent<\/i>*10,0))<\/span>.<\/p>\n<\/div>\n<p>I've attempted to figure out a way to change the color of the data bars conditionally, but the best I've been able to come up with instead is to use a 3-color scale in addition to a gray data bar. This can help if you want to use the color to signal an incomplete task.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/conditional-data-bars-using-3-color-scale.png\" alt=\"Conditional Data Bars Using 3-Color Scale\" width=\"455\" height=\"186\" class=\"aligncenter imgshadow\"><\/p>\n<div class=\"clear\"><\/div>\n<h2 id=\"highlight-every-other-row\">How to Highlight Every Other Row Using a Formula<\/h2>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/conditional-format-example-to-highlight-every-other-row.png\" alt=\"Conditional Format Example To Highlight Every Other Row\" class=\"aligncenter imgshadow\"><\/p>\n<p>Highlighting every other row can help make tables more readable like in the <a href=\"\/ExcelTemplates\/expense-tracker.html\">expense tracking<\/a> example. To do this in Excel (without needing the Tables feature), you can create a formula-based conditional formatting rule.<\/p>\n<div class=\"alignright\">\n<img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/highlight-every-other-row-using-conditional-formatting.png\" alt=\"Highlight Every Other Row Using Conditional Formatting\" class=\"imgshadow\">\n<\/div>\n<ol>\n<li>Select the cells you want to format (except the header).<\/li>\n<li>Go to <b>Home<\/b> &gt; <b>Conditional Formatting<\/b> &gt; <b>New Rule<\/b>.<\/li>\n<li>Select \"Use a formula to determine which cells to format.\"<\/li>\n<li>Enter the formula <span class=\"formula-in-text\">=MOD(ROW(),2)=0<\/span>.<\/li>\n<li>Click on <b>Format<\/b> then select a color in the <b>Fill<\/b> tab.<\/li>\n<\/ol>\n<p>Likewise, to highlight every other column, you could use the formula <span class=\"formula-in-text\">=MOD(COLUMN(),2)=0<\/span>.<\/p>\n<p>This technique allows you to insert and copy\/paste rows without having to update the background colors manually, but it has one major drawback: conditional formatting overrides manual formatting.<\/p>\n<p class=\"pull-quote\"><b>Key Point:<\/b> Conditional formatting overrides manual formatting.<\/p>\n<p>This means that if you want to change the fill color of a cell in your table, but the conditional formatting rule is already changing the fill color, you won't see the change that you are making. You ARE editing the fill color, but you won't see the change because conditional formatting is overriding your format.<\/p>\n<p>If you want to manually change the fill colors in your table AND highlight every other row, you may need to use the <b>Format as a Table<\/b> feature in Excel.<\/p>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTE<\/span> <span style=\"color:darkred;\"><b>Google Sheets<\/b><\/span> has a feature listed right underneath Conditional Formatting called <b>Alternate Colors<\/b>. This works separate from conditional formatting and DOES NOT override custom formatting! I LOVE that!<\/p>\n<\/div>\n<div class=\"clear\"><\/div>\n<h2 id=\"based-on-another-cell\">Conditional Formatting Based on Another Cell<\/h2>\n<p>To change the format of a cell based on another cell, or based on a range of cells, use a <b>formula rule<\/b> to determine which cells to format. Formulas can be simple or very complex. You can use most of the standard spreadsheet functions such as IF(), AND(), MATCH(), SEARCH(), COUNTIF(), SUMIF(), etc.<\/p>\n<h3 id=\"formula-rule\">How to Create a Formula Rule<\/h3>\n<div class=\"alignright\">\n<img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/how-to-create-a-formula-based-conditional-formatting-rule-in-excel.png\" alt=\"How to Create a Formula-Based Conditional Formatting Rule in Excel\" width=\"373\" height=\"366\" class=\"imgshadow\">\n<\/div>\n<p>To create a formula rule, select \"<b>Use a formula to determine which cells to format<\/b>\" after clicking on New Rule from the Conditional Formatting menu or from within the Rule Manager.<\/p>\n<p>A formula rule is activated when the formula returns TRUE.<\/p>\n<p>For example, <span class=\"formula-in-text\">=G27&lt;TODAY()<\/span> will apply the format when the date in cell G27 is overdue (less than today's date).<\/p>\n<p>To format based on multiple conditions, use the <b>AND()<\/b> function in your formula. For example, <span class=\"formula-in-text\">=AND(A1&gt;10,A1&lt;20)<\/span> will apply a format when cell A1 is between 10 and 20.<\/p>\n<p><b>Formula Rules can be very tricky!<\/b> When using relative references (references without dollar signs in front of the column letter or row number), you need to write your formula based on the top-left cell in the Applies To range. When you are creating the rule for the first time, write your formula based on the top-left cell that you have selected.<\/p>\n<p class=\"pull-quote\"><b>IMPORTANT:<\/b> Always write the formula based on the top-left cell in the Applies To range!<\/p>\n<div class=\"clear\"><\/div>\n<h3 id=\"highlight-an-entire-row\">How to Highlight an Entire Row<\/h3>\n<p>The <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/work-breakdown-structure.html\">Work Breakdown Structure<\/a> template shows an example of highlighting an entire row when the value in the Level column is equal to 1.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/how-to-highlight-an-entire-row-based-on-one-cell.png\" alt=\"How to Highlight an Entire Row Based on One Cell\" class=\"aligncenter imgshadow\"><\/p>\n<p>The formula we are using is <span class=\"formula-in-text\"><b>=($B6=1)<\/b><\/span>, and this applies to all the cells in the range <b>$B$6:$G$30<\/b>. One formula to rule them all! But how does it work?<\/p>\n<p>Behind the scenes, Excel is essentially copying this formula to each of the cells in the Applies To range. When a formula is copied, relative references (no dollar sign) will change. This means that for cell C15, the formula would become <span class=\"formula-in-text\">=($B15=1)<\/span>. Column B stays the same (because of the dollar sign in front of the B), but the row changes.<\/p>\n<p class=\"pull-quote\"><b>Key Point:<\/b> In Conditional Formatting Rules, absolute references stay the same ($B$1) and relative references (A1) change as Excel applies the formula to each cell in the Applies To range.<\/p>\n<p>If you aren't familiar with how absolute and relative references work, you may want to learn more about that. It's absolutely critical for understanding formula-based conditional formatting.<\/p>\n<div class=\"clear\"><\/div>\n<h3 id=\"highlight-based-on-a-range\">How to Highlight Cells Based on a Range<\/h3>\n<p>You can use the MATCH() function within a conditional formatting formula rule to highlight cells whose values are found in another range or list.<\/p>\n<p>For example, in the <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/yearly-calendar.html\">Yearly Event Calendar<\/a>, dates are highlighted in the calendar if the date is found in the list of holidays, as shown in the image below.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/conditional-formatting-based-on-a-range-of-dates.png\" alt=\"Conditional Formatting Based on a Range of Dates\" class=\"aligncenter imgshadow\"><\/p>\n<p>The formula is <span class=\"formula-in-text\">=<b>MATCH<\/b>(<i>this_date<\/i>,<i>date_range<\/i>,0)<\/span> where <i>this_date<\/i> is A10 (a relative reference to the top-left cell in the Applies To range), and <i>date_range<\/i> is $Y$9:$Y$300 (an absolute reference to the list of holidays in column Y).<\/p>\n<p>The MATCH formula only returns TRUE if it finds <i>this_date<\/i> within <i>date_range<\/i>. (See <a href=\"https:\/\/www.vertex42.com\/blog\/excel-formulas\/vlookup-and-index-match-examples.html\">VLOOKUP and INDEX-MATCH Examples<\/a> to learn about lookup formulas).<\/p>\n<div class=\"clear\"><\/div>\n<h3 id=\"highlight-values-not-in-a-range\">How to Highlight Values NOT in a Range<\/h3>\n<p>In some spreadsheets, I use conditional formatting to highlight values that are NOT within another range. Along with Data Validation, this can help with error checking.<\/p>\n<p>The formula for the rule is <span class=\"formula-in-text\">=<b>ISERROR<\/b>(<b>MATCH<\/b>(<i>this_cell<\/i>,<i>other_range<\/i>,0))<\/span>. This returns TRUE when MATCH does not find <i>this_cell<\/i> within <i>other_range<\/i>.<\/p>\n<p>I've used this technique in my original <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/money-management-template.html\">Money Management Template<\/a> to verify budget categories.<\/p>\n<h2 id=\"create-a-gantt-chart\">How to Use Conditional Formatting to Create a Gantt Chart<\/h2>\n<p>Watch the new Video Series: <a href=\"https:\/\/www.vertex42.com\/ExcelTips\/how-to-make-a-gantt-chart-in-excel.html\"><strong>How to Make a Gantt Chart in Excel<\/strong><\/a>!<\/p>\n<p>Most of my <a href=\"\/ExcelTemplates\/excel-gantt-chart.html\">Gantt Chart Templates<\/a> use a combination of many conditional formatting rules. The example below (from the <a href=\"\/ExcelTemplates\/simple-gantt-chart.html\">Simple Gantt Chart<\/a>) demonstrates (1) how to create the bars that show the date range for each task and (2) how to show the current date using a red border.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/how-to-use-conditional-formatting-to-create-a-gantt-chart.png\" alt=\"How to Use Conditional Formatting to Create a Gantt Chart\" width=\"600\" height=\"472\" class=\"aligncenter border imgshadow\"><\/p>\n<p><b>Rule 1<\/b> uses the formula <span class=\"formula-in-text\">=AND(<i>task_end<\/i>&gt;=G$5,<i>task_start<\/i>&lt;G$5+1)<\/span> where <i>task_end<\/i> is a relative named range defined as $D7 and <i>task_start<\/i> is a relative named range defined as $C7. Using named ranges isn't necessary. The formula could be written as <span class=\"formula-in-text\">=AND($D7&gt;=G$5,$C7&lt;G$5+1)<\/span><\/p>\n<p><b>Rule 2<\/b> can use the same formula, with today's date in place of both <i>task_end<\/i> and <i>task_start<\/i>. Or, the rule could be as simple as <span class=\"formula-in-text\">=G$5=TODAY()<\/span>.<\/p>\n<p>The proper use of absolute and relative ranges is absolutely critical here. So, pay special attention to the placement of the dollar signs.<\/p>\n<p><!--\n\n\n<p>The key to understanding the formula in Rule 1 is to pick a cell somewhere in the gantt chart and ask yourself \"Should this cell be highlighted?\" For example, cell N9 should be highlighted if the following two conditions are true:<\/p>\n\n\n\n\n<ol>\n\n\n<li>The <b>end<\/b> of the task ($D9) is on or after the date in N$5 (assuming the the task starts at the beginning of the day).<\/li>\n\n\n\n\n<li>The <b>start<\/b> of the task ($C9) is before the start of the next period. In this case, the start of the next period is the next day, calculated by N$5+1.<\/p>\n\n\n<\/ol>\n\n\n--><\/p>\n<div class=\"note-box\">\n<p><span class=\"note-label\">Note<\/span> You may notice that row 7 is hidden in the screenshot. The reason for hiding the first row of the gantt chart is so that if a person inserts a row above row 8, it will use the formatting from row 7 rather than the formatting from row 6.<\/p>\n<\/div>\n<h3>Using a \"No-Format Stop\" Rule<\/h3>\n<p>In some of my Gantt charts, such as the <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/construction-schedule.html\">Construction Schedule<\/a>, the user can pick the color of each bar. Each different color requires a separate conditional formatting rule.<\/p>\n<p>One way to do this would be to add the color condition to each rule like this: <span class=\"formula-in-text\">=AND(<b>$D7=\"red\"<\/b>,<i>task_end<\/i>&gt;=G$5,<i>task_start<\/i>&lt;G$5+1)<\/span>.<\/p>\n<p>However, I often prefer to use what I call a \"No-Format Stop\" Rule. Instead of saying where you want the color to be applied, this rule defines where the color should NOT be, and stops if true.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/no-format-rule-for-advanced-conditional-formatting-logic.png\" alt=\"Using a No-Format Rule for Advanced Conditional Formatting Logic\" width=\"628\" height=\"300\" class=\"aligncenter border imgshadow\"><\/p>\n<p>This No-Format Stop rule can be as simple as adding NOT() around the main gantt chart rule like this <span class=\"formula-in-text\">=<b>NOT<\/b>(AND(<i>task_end<\/i>&gt;=G$5,<i>task_start<\/i>&lt;G$5+1))<\/span>. It's called a \"No-Format\" rule because no formatting is applied. Its only purpose is to prevent the rules that come after it from being applied.<\/p>\n<p>The following rules then need to only check for what color to assign. In the example, the color is entered in column D. When cell D7 is blank, the bar will be gray. When D7=\"B\", the bar will be blue ... and so on.<\/p>\n<div class=\"clear\"><\/div>\n<h2 id=\"data-bars-two-groups\">Use Data Bars to Compare Two Groups<\/h2>\n<p>Charts like the population example below are popular for comparing two different groups.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/data-bars-for-comparing-two-groups.png\" alt=\"Data Bars for Comparing Male and Female Population\" width=\"298\" height=\"277\" class=\"aligncenter border imgshadow\"><\/p>\n<p>The trick to creating a chart like this with Data Bars is to use two different columns. The settings for the Male column use the \"Right-to-Left\" direction as shown in the image below.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/settings-for-data-bar-comparisons-right-to-left.png\" alt=\"Settings for Data Bar Comparisons\" width=\"377\" height=\"311\" class=\"aligncenter border imgshadow\"><\/p>\n<div class=\"caution-box\">\n<p><span class=\"caution-label\">!!!<\/span> The <b>column widths<\/b> and the <b>Min\/Max values<\/b> need to be identical for both columns of data, or the visual display will misrepresent the numbers. In this case, the Minimum is 0 and the Maximum is 25 for both the blue data bars and the red data bars.<\/p>\n<\/div>\n<p>You could argue that this type of graph is not ideal for making exact comparisons between the Male and Female population. For example, if the numbers weren't included in the chart, could you tell that for Age 40 to 49 there were more females (20.48 vs. 20.14)? No, you couldn't. The bars look like they are the same length.<\/p>\n<p>Our brains make better length comparisons when two things are aligned and parallel. The next chart uses Data Bars arranged a little differently. Now you CAN see the difference between the Male and Female population.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/data-bars-for-comparing-two-groups-better.png\" alt=\"Data Bars for Comparing Male and Female Population - Better\" width=\"296\" height=\"425\" class=\"aligncenter border imgshadow\"><\/p>\n<h2 id=\"link-min-max-to-cells\">Control Scaling by Linking Min\/Max to Cells<\/h2>\n<p>A great solution for controlling the scaling for Data Bars and Color Scales is to link the Minimum and Maximum values to cells that you can edit. Using this approach, you can have different rules using the same Min\/Max values. Here's an example:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/animation-data-bars-minimum-and-maximum-linked-to-cells.gif\" alt=\"Animation - Data Bars Scale Linked to Cells\" width=\"340\" height=\"260\" class=\"aligncenter border imgshadow\"><\/p>\n<p>To do this, edit the rule settings with cell references like this:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/data-bars-minimum-and-maximum-linked-to-cells.png\" alt=\"Data Bars Minimum and Maximum Linked to Cells\" width=\"377\" height=\"150\" class=\"aligncenter border imgshadow\"><\/p>\n<p>The green data bar and blue data bars in this example are separate rules, but both rules use Min\/Max values linked to cells C139 and D139.<\/p>\n<div class=\"clear\"><\/div>\n<h2 id=\"custom-number-formats\">Change Number Formats via Conditional Formatting<\/h2>\n<p>Some tricks I like to use in my templates involve changing the custom number format via conditional formatting based on user-selected options. If you aren't already familiar with how to create custom number formats, I would encourage you to read the article <a href=\"https:\/\/www.vertex42.com\/blog\/excel-tips\/custom-number-formats-in-excel.html\">Custom Number Formats in Excel<\/a>.<\/p>\n<p>Suffice it to say for now that you assign custom number formats by going to the Number tab when editing the Format for a conditional formatting rule.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/custom-number-formats-via-conditional-formatting.png\" alt=\"Custom Number Formats via Conditional Formatting\" width=\"530\" height=\"499\" class=\"aligncenter imgshadow\"><\/p>\n<h3 id=\"change-date-formats\">Change Date Formats to d\/m\/yyyy<\/h3>\n<p>If you are sharing a file with people from different countries, you may want a simple way to switch the display of dates from m\/d\/y to d\/m\/y. I use this technique in <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/gantt-chart-template-pro.html\">Gantt Chart Template Pro<\/a>.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/changing-date-format-using-conditional-formatting.png\" alt=\"Changing Date Formats using Conditional Formatting\" width=\"513\" height=\"242\" class=\"aligncenter imgshadow\"><\/p>\n<p>The date in cell F131 displays a date as <span class=\"mono\" style=\"border:1px solid #555;padding:0 5px;\"><b>m\/d\/yyyy<\/b><\/span> by default, but when the user selects \"dmy\" in cell D131, the CF rule changes the format to <span class=\"mono\" style=\"border:1px solid #555;padding:0 5px;\"><b>d\/m\/yyyy<\/b><\/span>.<\/p>\n<p>You could set up similar options to switch time formats between standard and 24-hour time, or change the display of numbers from decimal to fractions.<\/p>\n<div class=\"note-box\">\n<p><span class=\"note-label\">Note<\/span> <i>Developers:<\/i> To avoid having to switch between date formats, use the built-in date formats marked with the asterisk (*) when you can. From Excel: \"Date formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified for the operating system.\"<\/p>\n<\/div>\n<h3 id=\"hide-zero-values\">Hide Zero Values<\/h3>\n<p>This example shows 3 different ways to hide zero values using conditional formatting.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/how-to-hide-zero-values-with-conditional-formatting.png\" alt=\"How to Hide Zero Values using Conditional Formatting\" width=\"373\" height=\"145\" class=\"aligncenter imgshadow\"><\/p>\n<ul>\n<li>The 2nd column hides the zero values by changing the font color to white.<\/li>\n<li>The 3rd column changes the custom number format to <span class=\"mono\" style=\"border:1px solid #555;padding:0 5px;\">\"&nbsp;-&nbsp;\"<\/span>.<\/li>\n<li>The 3rd column changes the custom number format to <span class=\"mono\" style=\"border:1px solid #555;padding:0 5px;\">\"&nbsp;\"<\/span>.<\/li>\n<\/ul>\n<p>I personally prefer replacing zeros with a dash because otherwise the cell looks blank, and cells that look blank tend to be deleted or overwritten because you think they are empty.<\/p>\n<h3 id=\"automatic-indenting\">Automatic Indenting<\/h3>\n<p>A custom number format will allow you to add text characters before or after the number, so you can simulate indenting by adding a number of spaces before your text using the format code <span class=\"mono\" style=\"border:1px solid #555;padding:0 5px;\"><b>\"   \"@<\/b><\/span>.<\/p>\n<p>I use this technique in both the free <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/work-breakdown-structure.html\">Work Breakdown Structure<\/a> template, and the premium <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/gantt-chart-template-pro.html\">Gantt Chart<\/a> Template.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/automatic-indenting-via-conditional-formatting.png\" alt=\"Automatic Indenting via Conditional Formatting\" width=\"590\" height=\"220\" class=\"aligncenter imgshadow\"><\/p>\n<p>In this example, the formula is counting the number of decimal points in the WBS number. One rule adds 3 spaces before the text if it finds 1 decimal point. A second rule adds 6 spaces before the text if it finds 2 decimal points. See the article <a href=\"https:\/\/www.vertex42.com\/blog\/excel-formulas\/text-formulas-in-excel.html#count-spaces\">Text Manipulation Formulas<\/a> to learn how this formula counts decimal places.<\/p>\n<h3 id=\"thousands-millions\">Display 23K, 23M, or 23B Based on the Value<\/h3>\n<p>Displaying large numbers as thousands, millions, or billions is VERY common, but how do you automatically display \"K\" or \"M\" or \"B\" depending on the value of the number? Using 3 different CF rules with the custom format codes <span class=\"mono\" style=\"border:1px solid #555;padding:0 5px;\"><b>0.0K<\/b><\/span>, <span class=\"mono\" style=\"border:1px solid #555;padding:0 5px;\"><b>0.0M<\/b><\/span>, and <span class=\"mono\" style=\"border:1px solid #555;padding:0 5px;\"><b>0.0B<\/b><\/span>.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/display-thousands-millions-billions-with-cf.png\" alt=\"Display Thousands, Millions, or Billions using CF Rules\" width=\"580\" height=\"190\" class=\"aligncenter imgshadow\"><\/p>\n<h3 id=\"custom-icons\">Display Custom Icons<\/h3>\n<p>Excel has a limited number of icons available for Icon Sets. However, if you use unicode symbols and emojis, you can create your own icons using multiple CF rules. The format codes in this example are simply <span class=\"mono\" style=\"border:1px solid #555;padding:0 5px;\"><b>0*&nbsp;X<\/b><\/span> where X is the unicode character. To place the icon on the left with the number on the right, you could use <span class=\"mono\" style=\"border:1px solid #555;padding:0 5px;\"><b>X*&nbsp;0.00<\/b><\/span> instead.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/custom-icons-using-emojis-in-cf-rules.png\" alt=\"Custom Icons Using Emojis in CF Rules\" width=\"590\" height=\"246\" class=\"aligncenter imgshadow\"><\/p>\n<p>Check out the <a href=\"https:\/\/www.vertex42.com\/calendars\/moon-phase-calendar.html\">Moon Phase Calendar<\/a> to see how it uses conditional formatting to replace dates in a calendar with moon phase unicode characters.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/example-using-conditional-formatting-to-display-moon-phases-in-a-calendar.png\" alt=\"Using Conditional Formatting to Display Moon Phases\" width=\"470\" height=\"195\" class=\"aligncenter imgshadow\"><\/p>\n<div class=\"clear\"><\/div>\n<h2 id=\"copy-conditional-formatting\">How to Copy Conditional Formatting<\/h2>\n<p>The simple answer is that unless you tell Excel or Google Sheets NOT to, conditional formatting will be copied whenever other formatting is copied. For example, when you copy\/paste cells, when you use the format painter tool, and when you insert rows and columns.<\/p>\n<p>Copying conditional formatting may not always behave the way you expect it to. Sometimes that may not matter, but in other cases it may cause significant errors in how the data is interpreted. So, it's always a good idea to check your conditional formatting rules after copying\/pasting.<\/p>\n<p>One of the main problems I've seen occurs when copying a row and inserting the copied row. Rules may be split, resulting in separate rules for different ranges when you expect there to be only one rule for the entire range. This can cause problems with color scales and data bars, especially.<\/p>\n<p>To avoid the problem of splitting CF rules, when pasting you can use Paste Special and choose the \"All Merging Conditional Formats\" option.<\/p>\n<div class=\"clear\"><\/div>\n<h2 id=\"volatile\">Conditional Formatting is Volatile<\/h2>\n<p>If you use a lot of conditional formatting in your worksheet, or the rules involve inefficient formulas, you may notice Excel appear to slow down. That is because Conditional Formatting is volatile - meaning that the rules are evaluated every time the display refreshes.<\/p>\n<p>To experiment with this concept, I built the following example. The fire in the display will flicker every time rules are evaluated, allowing you to test actions that cause the display to refresh.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/excel-training\/conditional-formatting-is-volatile.gif\" alt=\"Conditional Formatting is Volatile\" width=\"450\" height=\"420\" class=\"aligncenter imgshadow\"><\/p>\n<p class=\"downloadlink\"><span class=\"icon16 excel\"><\/span><a href=\"\/Files\/examples\/VolatileCF.xlsx\" onClick=\"ga('send','event', 'Downloads', 'Examples', 'VolatileCF.xlsx');\" rel=\"nofollow\"><b>Download the Example File<\/b><\/a> (VolatileCF.xlsx)<\/p>\n<h2>Wrap Up and Final Tips<\/h2>\n<p>If you like this article, or use some of these ideas, please share the link to this article via your website and social media!<\/p>\n<p>I didn't talk about all the possible uses for conditional formatting, and not even some of the most common uses, such as making negative values red and highlighting based on specific text values. That is partly because many of these techniques are extremely easy to implement using the built-in options.<\/p>\n<p><b>If you have questions<\/b>, please go ahead and submit a comment below.<\/p>\n<p><b>If you'd like to try some geeky fun<\/b>, check out the <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/mine-blaster-game.html\">Mine Blaster 1000<\/a> game. It makes use of multiple overlapping conditional formatting rules to create a game something roughly like Minesweeper (without any VBA).<\/p>\n<p><a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/mine-blaster-game.html\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/ExcelTemplates\/Images\/mine-blaster.png\" alt=\"Mine Blaster 1000 - Using Conditional Formatting\" style=\"width:100%\" class=\"aligncenter imgshadow\"><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Learn over 20 different ways to use conditional formatting in Excel to do things like add color scales for data analysis, add data bars for in-cell charts and progress bars, and create a gantt chart.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[78],"tags":[],"class_list":{"0":"post-3509","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-excel-tips"},"_links":{"self":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3509","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/comments?post=3509"}],"version-history":[{"count":0,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3509\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media?parent=3509"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/categories?post=3509"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/tags?post=3509"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}