Sparkline and Conditional formatting are two powerful Excel feature in Excel that can help in gaining quick insights and improve data visualisation.
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.
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.
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.
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.
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.
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.
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.
Step 8: Click OK.
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
- Clear rules from selected cells
This option is used clear all conditional formatting giving to the selected cells.
- 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.
Step 10:Create sparkline dialogue box appears. Under the dialogue box select the data range. In this case, the range is “C14: G19”
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.
Our table is ready. Such a type of table is informative and easier to understand and could be presented to our clients as well.
We can highlight every point using different colours with help of Marker Colour option.
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)
- Clear Selected Sparkline
This option is used clear all Sparkline in the selected cells.
- 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