Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
Sparklines in excel are like a chart in a cell itself, they are tiny visual representations which show the trend of the data whether increasing or decreasing, to insert a sparkline we need to select the cell where we want the sparkline and in the insert tab in the lines section click on sparklines after that we can choose any one of the styles of sparklines.
Excel Sparklines (Table of Contents)
What are the Sparklines in Excel?
- Sparklines are most useful when multiple charts are required to represent the data. This is because the chart will take most of the spreadsheet space and we can save this space by using Sparklines in excel.
- Suppose if we have the data of profit and loss for the first two quarters of 10 sellers and we want to Visualize the trend of profit or loss they make in two quarter’s, then we cannot do this with the help of single traditional charts. This can be possible only if Sparklines are inserted for each of the sellers.
How to Create Sparklines in Excel?
Here we discuss how to create sparklines in excel along with examples.
Example #1 – Using a line in Excel Sparkline
By creating a Sparkline, we meant that we need to insert something in an excel and whenever we need to insert or add something we also need to go to the “Insert” tab of excel.
So first we need to go to the “Insert” tab.
From the ribbon, go to “INSERT” option and choose the line chart from the Sparkline options.
Step 2: From the window, enter or select the “Data Range” that has the data.
Step 3: Enter the location of the cell where the Sparkline is to be created, this is not usually asked in case of creating a chart as a chart is an object for excel but Sparkline is not an object and needs a location to insert in excel.
Step 4: After the location from where the data has to be picked and the location of a cell in which Sparkline is to be inserted in done, then we will get the below Sparkline in excel.
Since we have selected the “line” method for creating a Sparkline, the Sparkline will be very identical to the “Line chart”.
Example #2 – Using Column in Excel Sparkline
Step 1: Since this time we are creating a column in Sparkline, we need to select the column instead of a line from the available options.
Step 2nd and 3rd will be the same as in the case of “Using a line in Sparkline”.
Step 4: The column Sparkline will look like as below.
Example #3 – Using Win/Loss in Excel Sparkline
Step 1: Select the Win/Loss from the available Sparkline options.
Step 2nd and 3rd will be same as explained before.
Step 4: The win/loss Sparkline will look as below.
Example #4 – If in case we have Blank cells or zero value cells in the data
If we have blank cells in the data then the Sparklines will be broken and will look separated as below.
To correct this type of situation we need to change how the empty cell is treated by Sparkline.
Step 1: Select the Sparkline and go the Insert option. From the Design, option selects the “Edit Data” option.
Step 2: From the Edit Data option choose the option of “Hidden and empty cells”.
Step 3: From the available options, we can choose how the gaps are to be treated.
Step 4: Below is how each of the options will treat gaps.
If the data has a gap it makes a broken Sparklines, follow the same steps of Method (1,2 & 3). Below are the Examples of Broken Sparklines.
To Make a “Markers” in the Sparklines, Go to Design tab and Click on the option “Markers”, after inserting sparklines.
Then Your Marker added Sparklines will look like this.
To Show Axis in Sparklines, Go to Design tab and Click on “Axis” then, Click on Show Axis.
Axis added Sparklines will look as given below.
Explanation of Sparklines in Excel
When we only need the visualization instead of full features of traditional charts, then we should use Sparklines instead of charts as sparklines also have many features that are enough to visualize the data.
Sparklines works the same as a chart works, but the only difference is this that Sparklines is inserted in the cell and a chart is always outside a cell and is an object to excel. Getting started with a Sparkline is easy as this just needs the data to be selected and then inserting a Sparkline from the ribbon. When we create a Sparkline in excel, then we have many options about how the Sparklines are to be represented and hence we can choose to use a line, columns or win-loss methods. If we use the line Sparkline then the chart that we get will be more identical to the line chart and in the case of column chart Sparkline, this will match the traditional column chart.
This should be remembered that the column and line chart can be used in case we have data and we need to show the change in the magnitude of that data via Sparkline. We cannot use the Win/Loss type if we want to show the magnitude of change in data as this type of chart works on the “True” or “False” method only and are not capable of showing a change in magnitude of data.
Things to Remember
- Sparklines are not an Object. These are inserted a cell and not on the worksheet area as done in case of charts, that are inserted as an object and on the worksheet.
- Even if Sparkline is created in a cell, we can still type in that cell.
- A Sparkline needs to be deleted from the menu and cannot be deleted with the click of “Delete” button.
- The Height and width of the Sparkline depend on the Height and width of the cell in which it is inserted. This means that Sparkline look will change if any change is made to the width, height of the cell.
- We should not use the “Win/loss” method of Sparkline if we need to show the magnitude of change. This is because they just represent the “True” and “False” situations.
This has been a guide to Sparklines in Excel. Here we discuss how to Create Sparklines in Excel using sparklines options such as Line, Column, Win/Loss, Marker, Broken lines and Axis along with practical examples and downloadable excel template. You may learn more about excel from the following articles –