MODULE-3
PIVOT TABLE AND PIVOT CHART
Sparklines and data bars
Sparklines are miniature, condensed charts that provide a quick visual
representation of data trends and patterns within a single cell in Microsoft Excel.
These tiny graphs allow you to analyze data at a glance, without the need to
create full-sized charts.
To Insert Sparkline:
Select the cell where you want the Sparkline.
Go to Insert → Sparklines group.
Choose from:
Line Sparkline (displays trends over time).
Column Sparkline (compares values).
Win/Loss Sparkline (shows gains and losses).
Select the range of data to base the Sparkline on.
Click OK to insert.
Customize the Sparkline
To format the sparkline, select the sparkline you want to format and then select the Sparkline option
on the menubar as shown in the picture below:
Using show submenu
In the show submenu, there are 6 options :
High Point: Marks the maximum point on the sparkline.
Low Point: Marks the minimum point on the sparkline.
First Point: Marks the leftmost ( first ) point on the sparkline.
Last Point: Marks the rightmost ( last ) point on the sparkline.
Markers: Mark all the edge points on the sparkline.
Negative Points: Marks the negative points in the graph with a different color which is illustrated in the
figure below:
Highlighting Data Points in Sparklines
While Sparklines provide a concise view of data trends over time, you can further enhance their meaning by
adding markers a highlighting to emphasize critical data points.
For instance, you can highlight the maximum and minimum data points, the first and the last data points, or
even negative data points to draw attention to specific values.
Below is the example showcasing the maximum and minimum data points in both line and column
sparklines:
Sparklines Color and Style
Excel offers various style and color options to modify the appearance of sparklines. You can change the color of lines or
columns as well as markers.
Hidden and Empty Cells
When working with the line sparklines in a dataset that contains empty cells, you may observe that the
sparkline displays a gap to indicate the absence of data in those cells. This gap can sometimes disrupt the
continuity o the sparkline and affect data visualization. However, Excel provides effective ways to handle
hidden and empty cells within sparklines, ensuring a smooth and acute representation of data trends.
Ignoring Hidden Cells
By default, Excel considers hidden cells in sparklines, leading to gaps in the line when data is hidden. To
overcome this, you can instruct Excel to ignore hidden cells while creating sparklines. Follow the below
steps to ignore the hidden cells:
Step 1: Click the cell that has the sparkline.
Step 2: Click the Design Tab.
Step 3: Click on the Edit data Option.
Step 3: In the drop-down, select the ‘Hidden & Empty Cells’ option.
By enabling this setting, sparklines will disregard data in hidden cells, maintaining the line’s continuity
Delete Sparklines
It is not possible to delete a sparkline by selecting the cell and hitting the delete key. To delete a
sparkline follow the below steps:
Step 1: Select the cell that has the sparkline that you want to delete.
Step 2: Click the Sparkline Tools Design tab.
Step 3: Click the clear option
Data Bars
Data Bars are premade types of conditional formatting in Excel used to add colored bars to cells in a range to
indicate how large the cell values are compared to the other values.
How to Add Data Bars in Excel:
1. Select the Range: Highlight the cells where you want to apply the data bars.
2. Go to Conditional Formatting: Click on the "Home" tab, then choose "Conditional Formatting" from the ribbon.
3. Select Data Bars: Hover over "Data Bars" under the menu.
4. Choose a Style: Pick a Gradient Fill or Solid Fill with your preferred color.
5. Adjust Settings (Optional): Click "More Rules" to:
1. Change the minimum and maximum values.
2. Modify the bar direction.
3. Customize bar appearance (e.g., show only bars or keep numbers visible).
Advanced chart techniques(combination charts, dual-axis charts)
When standard charts aren’t enough, advanced chart techniques like
Combination Charts and Dual-Axis Charts help visualize complex data
effectively.
Combination Charts
A combination chart in Excel combines two or more charts into a single
visual. It's a way to compare data from different categories over time.
How to create a combo chart in Excel
1)Select the data you want to use
2)Go to the Insert tab
3)In the Charts group, select Combo Chart
4)Choose the chart type you want to use
What types of charts can be combined?
Bar charts and line charts are commonly used together
Area charts or dot charts can also be used
Benefits of a combo chart
You can display multiple measures with different scales on a single
chart
2. Dual-Axis Charts
A dual axis chart in Excel, also known as a combo chart, displays two sets of data on a single graph. The
two sets of data share an X axis but have separate Y axes.
To create a dual axis chart in Excel:
1) Select the data you want to use
2) Click the Insert tab
3) Click the Combo button
4) Select Create Custom Combo Chart
5) Click the Secondary Axis box for the data you want to display on the alternate axis
6) Click OK
Pivot Tables in Excel
Pivot tables are one of Excel's most powerful features. A pivot
table allows you to extract the significance from a large, detailed
data set.
Our data set consists of 213 records and 6 fields. Order ID,
Product, Category, Amount, Date and Country
Insert a Pivot Table
To insert a pivot table, execute the following steps.
1. Click any single cell inside the data set.
2. On the Insert tab, in the Tables group, click PivotTable.
The following dialog box appears. Excel automatically selects the data for you. The
default location for a new pivot table is New Worksheet.
3. Click OK.
Drag fields
The PivotTable Fields pane appears. To get the total amount exported of
each product, drag the following fields to the different areas.
1. Product field to the Rows area.
2. Amount field to the Values area.
3. Country field to the Filters area
Below you can find the pivot table. Bananas are our main export product. That's
how easy pivot tables can be!
Sort a Pivot Table
To get Banana at the top of the list, sort the pivot table.
1. Click any cell inside the Sum of Amount column.
2. Right click and click on Sort, Sort Largest to Smallest.
Result:
Filter a Pivot Table
Because we added the Country field to the Filters area,
we can filter this pivot table by Country. For example,
which products do we export the most to France?
1. Click the filter drop-down and select France.
Result: Apples are our main export product to France.
Configure the Pivot Table
After the pivot table is inserted, you will have the Pivot Table Field List or Pivot Table Editor
(depending on the tool you're using). This is where you configure what data to display in the rows,
columns, values, and filters.
In Excel (Pivot Table Field List):
•Rows: Drag a field (e.g., Region, Product) here to list unique values in rows.
•Columns: Drag a field here to create columns, such as Year, Category.
•Values: Drag a numeric field (e.g., Sales, Quantity) here to perform calculations (e.g., Sum,
Average).
•Filters: Use this to filter your data by a specific field (e.g., filter by date range or region).
Grouping and summarizing data in pivot tables
Grouping data in Pivot Table refers to a powerful data organization feature in Excel, allowing users to
effortlessly categorize information based on various criteria such as dates, numbers, and text fields.
Types Of Grouping Pivot Table Data in Excel
In the Pivot data table, data can be grouped based on Dates, Numbers, and Text values.
•Grouping by Date: We can group dates by months or years, months by years, etc.
•
•Grouping by Numbers: We can group numbers in a particular range band together.
•Grouping by Text: For text values, we need to group manually, otherwise, it will throw an error.
How to Group Dates in Pivot Tables in Excel By Month and Year
Step 1: Create a Pivot Table
Step 2: Right-Click a value and select a group.
Step 3: Select the Time Period Days and Months and
Click Ok
Step 4: Preview Pivor Data
The Pivot data table is grouped based on Month and
Date.
How to Group Dates in Pivot Tables in Excel by Week
By following the procedures listed below, you can arrange the entries in a Date field by week
Step 1: Use the Right-click on Dates in Pivot Table
Step 2: Select Group from the Pop-Up Menu
Step 3: Select Days from the ‘By’ list
Step 4: Select 7 in Number of Days
Step 5: Adjust week Range if Required
Step 6: Click OK
Grouping Numbers
Number Grouping in Pivot tables is a powerful feature that helps you to create a more concise and organized
summary of numerical data. Instead of listing numbers individually in a row field, you have the option to group
them together based on specific intervals or ranges, making it easier to analyze the data.
Step 1: Create a Pivot Table
Step 2: Right-click on a cell and select the group
Step 3: In the dialogue box, under by select the interval of
groups
Step 4: Click OK, pivot table data is grouped in intervals of
5
How to Group Text Items in Pivot Table
It is not possible to group text data using the above method in Excel. It will throw an error if we try to
do so. So, we have to select data manually that we want to group, and then group it.
Step 1: Create a Pivot Table.
Step 2: Select the Data Manually
Step 3: Right Click on Data and Select Group
Step 4: Preview the selected data will be vivble under
Group 1
Step 5: Rename the Group 1
How To Rename the Group in Pivot Table
You can also rename the group according to you. Follow the below steps to rename the Group
Step 1: Click the Group Heading
Step 2: Edit and Type a new Name
How To Add More Items to a Group in Pivot Table
This is a useful feature when you need to add some data after grouping. Follow the below steps:
Step 1: Select All Data in the Group
Step 2: Press Ctrl Key and Select data to be added in the Group
Step 3: Right-click on the selected data and Select Group
How to Ungroup Text Items in Pivot Table
After manually grouping the text items in a Pivot field, there might be instances where you need to
ungroup certain data or all of them. Excel provides easy methods to accomplish both tasks
efficiently.
How to Ungroup a Single Group
Step 1: Right-click on any item within the desired group.
Step 2: Click on “Ungroup”
How to Ungroup All Groups in Pivot Table
Step 1: Click the Group Heading
Step 2: Right-click on any items within the Selected groups.
Step 3: Click on “Ungroup”
Creating and customizing pivot charts
To create a PivotChart in Excel, you can first create a PivotTable, then select a cell in the PivotTable and
insert a chart. You can customize the PivotChart by using the Chart tab or the PivotChart Analyze tab.
Create a PivotChart
1. Select a cell in the PivotTable
2. Go to the Insert tab
3. Select the Insert Chart dropdown menu
4. Click any chart option
5. The chart will appear in the worksheet
Customize a PivotChart
1. Click anywhere in the chart
2. Use the options in the Chart tab to modify the chart
3. To hide field buttons, right-click on a button and select Hide Value Fields Buttons on Chart
4. To remove all field buttons, right-click on any button and select Hide All Field Buttons on
Chart
5. To change the stripe color, click the Format button, then click the Fill tab and choose a fill
color
Slicers and timelines
Slicers and timelines are powerful features in tools like Microsoft Excel or Power BI that allow for
interactive data analysis. They help users filter and manipulate data dynamically, enabling better
decision-making and data insights. Here's a brief overview of each, along with how to use them
effectively:
1. Slicers
A Slicer is a visual filter that allows you to segment data by specific categories or values. It is often
used in PivotTables and PivotCharts to provide an intuitive and interactive way to filter data
.
How to Use Slicers in Excel:
• Select your PivotTable or PivotChart.
• Go to the Insert tab.
• Click on Slicer in the Filters group.
• A dialog box will appear where you can choose the fields you want to use as filters. For
example, you might choose "Product Category" or "Region."
• Click OK to insert the slicer.
Steps to use Slicers in Excel
Step 1: First we need to convert all the data into a tabular format or a Pivot Table format because it is
impossible sometimes to directly apply slicers to data so first to need to convert them into a pivot table or
simply in table form
Step 2: Create Pivot Table
•Navigate to the Insert menu on the toolbar under the Tables section select PivotTable.
•A Dialogue box is open as shown below now select Table/Range of table.
•After that click “OK” you can see Pivot Table is created in the next sheet of the excel file.
Step 3: Now to create Slicers select any cell in the table Navigate to the insert option on the
toolbar then Select insert Slicer.
•A new window will appear asking for parameters. Select
parameters and click on “OK”.
•Now Slicers will be displayed for filter operations.
2. Timeline
A Timeline is similar to a slicer but is specifically designed to filter data based on dates. It’s
a great tool for time-based analysis, allowing you to filter by months, quarters, years, or
specific date ranges.
How to Use a Timeline in Excel:
1. Select a PivotTable that contains a date field.
2. Go to the Insert tab.
3. Click Timeline in the Filters group.
4. A dialog box will prompt you to select a date field from your data. Once chosen,
click OK.
Combining Slicers and Timelines for Interactive Analysis:
By using both slicers and timelines together, you can create a highly interactive and dynamic
dashboard that allows you to filter your data in multiple ways.
For example:
•Use a slicer to filter data by Product Category and a timeline to filter by Date. This allows you
to analyze sales trends for different products over specific time periods.
•By using slicers for categorical data and timelines for time-based data, you can drill deeper into
data insights without the need to manually adjust the data view every time.