Articles

Sparkline and Conditional formatting

Pinterest LinkedIn Tumblr

Sparkline and Conditional formatting are two powerful Excel feature in Excel that can help in gaining quick insights and improve data visualisation.

Sparkline and Conditional formatting

Often people work with numbers and a table, this is not something that can be present for visualisation. However with the help of sparkline and conditional formatting we can make our table in a presentable format.

Excel Sparkline is a small pictorial representation of the data that fits into one cell. Sparklines are used to visualize fluctuations in temperature, stock prices, periodical sales, and other variables expanding over a time frame. They are tiny yet informative.
On the other hand, Conditional Formatting helps to apply format based on specific criteria. In simple words, it formats cells, values, text, etc which meet the criteria specified.

Now that we know the power of sparkline and conditional formatting, let’s put it to use.
In this article, we remodel a table into a presentable manner using sparkline and conditional formatting

The following are the steps used to create tables

Step 1: Apply table, be on the data region and then Go to Insert Tab >>Table either Press Ctrl key + T Key to create a table.

Sparkline and Conditional formatting

Step 2: In this step, let us change the table design and remove the filter.
1. To apply table design be on the table and go to Table Design on the ribbon choose your Table style.
2. By default, Excel adds Filter to our header while creating a table.
To remove the Filter, be on the table Go to Data Tab>> Filter option under Sort&Filter, or Press Ctrl key+Shift key+ L key to remove or apply filter.

Sparkline and Conditional Formatting_1

Pro Tip

You can convert table into rage whilst retaining the style. Be on the table >>Table Design>> Convert to range under Tools.

Step 3:Under this step lets us find the variances between the actual and target.
To find:Variances=(Actual / Target,-1).
Note: To convert number to percentage : Go Home>> click “%” symbol under Number option.

Sparkline and Conditional formatting_12

How to use Conditional Formatting in table

Step 4: Select the columns which should be conditional formatted. Go to Home Tab >> Conditional Formatting. There various formatting options available you can choose as per your requirement. In this case, lets us use directional under Icon Sets option.

Sparkline and Conditional Formatting_3

Step 5:Excel automatically evaluates the data and allocates the Icon Sets, if you notice in the below image some of the negatives values are highlighted with the left arrow. To fix these issues lets us modify our Conditional Formatting rules.

Sparkline and Conditional Formatting_4

Step 6:Stay on the Table. Go to Home Tab >> Conditional Formatting>>Manage Rules. Conditional Formatting Rules Manager dialogue box pop-up as shown in the below image.

Sparkline and Conditional formatting-04
Sparkline and Conditional formatting-05

Step 7: Click on Edit Rule>> Edit Formatting Rule Dialogue box appears >>under Icon green up arrow for a value greater than 0, the yellow left arrow for the value the equal to 0, red down arrow for the value lesser than 0.

Note: Click on the Show Icon Only box to show icons alone in the cells and not numbers with icons.

Sparkline and Conditional formatting-06

Step 8: Click OK.

Sparkline and Conditional formatting-07

Pro Tip

If the user wishes to remove the conditional formatting (bring back to the old style), they can do so.
Simply select the cell/array which. Go to HOME >> Conditional Formatting >>Clear Rules > two options will be displayed as shown in 

Sparkline and Conditional formatting 1
  1. Clear rules from selected cells
    This option is used clear all conditional formatting giving to the selected cells.
  2. Clear rules from Entire sheet
    This option is used to clear all conditional formatting giving in the active spreadsheet.

How to use Sparklines in table

Step 9: Be on the empty cell (where you want the sparklines to be presented) and Go to Insert tab >>Line under Sparklines.

Sparkline and Conditional formatting-08

Step 10:Create sparkline dialogue box appears. Under the dialogue box select the data range. In this case, the range is “C14: G19”

Sparkline and Conditional formatting 2

Step 11:You can Customise the sparkline. Be on the cell containing sparkline and then go to Sparkline option from the ribbon. You can choose the style and design as required. In this case, I’m going to highlight the highest point and lowest point by checking the box next to the high point and low point under Show section.

Sparkline and Conditional formatting-10

Our table is ready. Such a type of table is informative and easier to understand and could be presented to our clients as well.

Sparkline and Conditional formatting 3

We can highlight every point using different colours with help of Marker Colour option.

Pro Tip

When you decide to remove a sparkline chart, you might be surprised to find that pressing the Delete key has no effect. The following are the steps to be followed for removing the sparkline.

  • Select the sparkline(s) want to be deleted.
  • Go to Sparkline tab>>Clear (two options will be displayed as shown in the below image)
Sparkline and Conditional formatting
  1. Clear Selected Sparkline
    This option is used clear all Sparkline in the selected cells.
  2. Clear Selected Sparkline Group
    This option is used clear all Sparkline in a group.

To know more on Excel check out our Articles Section.

To gain insight on chart -Click here

Related Posts

Write A Comment