{"id":4422,"date":"2019-01-22T11:10:18","date_gmt":"2019-01-22T16:10:18","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=4422"},"modified":"2019-01-22T11:20:14","modified_gmt":"2019-01-22T16:20:14","slug":"4422","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=4422","title":{"rendered":"Did you know \u2026 Excel can automatically highlight data for you?"},"content":{"rendered":"

Reading through large tables of data is inefficient \u2013 it\u2019s time consuming, error prone, and just not a heap of fun. Graphs are one way to visualize data \u2013 allowing you to quickly spot trends, outliers, etc. Excel offers another way to visually enhance data to make it more comprehensible \u2013 conditional formatting. Where some charts and graphs obscure the underlying data, conditional formatting allows the exact value to be quickly identified.<\/p>\n

Highlight your data. On the ribbon bar, select \u201cHome\u201d and click the drop-down for \u201cConditional Formatting\u201d.<\/p>\n

Select the logic to determine which cells are highlighted \u2013 we\u2019ll go through a few examples here, but click around on your own! To highlight cells that are higher than some value, select \u201cHighlight Cell Rules\u201d and then select \u201cGreater Than\u201d.<\/p>\n

In the window that appears, enter the number and select the colouring scheme. The prepopulated number will be the average<\/em> of the highlighted data. The changes are applied as you select formatting options, so you have an idea what it\u2019ll look like ahead of time. In this case, there are still a lot<\/em> of values higher than 125. I could increase my number to reduce the number of highlighted cells. When you have finished composing your formatting rule, click OK.<\/p>\n

<\/p>\n

And the format is applied to your data. You can apply multiple formats \u2013 add another format to turn anything below 25 green, make values between 100 and 124 yellow. Whatever you want.<\/p>\n

<\/p>\n

If you need to change your formatting rules, click on the \u201cConditional Formatting\u201d drop-down and select \u201cManage Rules\u201d.<\/p>\n

<\/p>\n

If your rules do not appear, change \u201cCurrent Selection\u201d at the top to \u201cThis Worksheet\u201d.<\/p>\n

<\/p>\n

You can also define custom rules. From the \u201cConditional Formatting\u201d drop down, select \u201cNew Rule\u201d.<\/p>\n

<\/p>\n

Again, select the logic used to determine which cells are formatted. Here, I am highlighting duplicated values. Click \u201cFormat\u201d to define how the highlighted cells should appear. Click \u201cOK\u201d to apply the formatting to your spreadsheet.<\/p>\n

<\/p>\n

Now every duplicated record is in green with a strike through the value.<\/p>\n

<\/p>\n

Formatting rules can be nuanced \u2013 here I am creating a custom formatting rule that uses a three-colour gradient based on where a value falls within a range.<\/p>\n

<\/p>\n

Now you can quickly compare each value by it\u2019s colour.<\/p>\n

<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

Reading through large tables of data is inefficient \u2013 it\u2019s time consuming, error prone, and just not a heap of fun. Graphs are one way to visualize data \u2013 allowing you to quickly spot trends, outliers, etc. Excel offers another way to visually enhance data to make it more comprehensible \u2013 conditional formatting. Where some …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[677],"tags":[675,708,709,691,729,731],"class_list":["post-4422","post","type-post","status-publish","format-standard","hentry","category-office-365","tag-did-you-know","tag-excel","tag-microsoft-excel","tag-microsoft-office","tag-microsoft-office-365","tag-ms-excel"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4422","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4422"}],"version-history":[{"count":2,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4422\/revisions"}],"predecessor-version":[{"id":4433,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4422\/revisions\/4433"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4422"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4422"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4422"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}