Pivot Table and Matplotlib Notes
1. Define grouping of items in a Pivot Table.
Grouping in a Pivot Table means combining similar items or data points into categories.
Example: Group sales data by month or quarter.
2. Define formatting in context of a Pivot Table.
Formatting in a Pivot Table means adjusting layout, font, and style for better readability and
presentation.
Purpose: To make the summarized data more understandable and visually appealing.
3. State any two methods to specify Pivot Table data.
1. Selecting data manually from a worksheet.
2. Using an external data source like Access or SQL database.
4. Difference between Pivot Table and Pivot Chart:
Pivot Table: Summarizes and organizes data in tabular form.
Pivot Chart: Represents Pivot Table data visually through charts.
Pivot Table shows numeric summary; Pivot Chart shows trends visually.
5. Role of slicers and timelines in data analysis:
Slicer: Used to filter Pivot Table data easily.
Timeline: Used to filter data based on dates.
Both help in dynamic data analysis and quick comparison.
Example: Filter sales by region or by year.
6. Create Pivot Table to summarize total sales by Category:
Steps:
1. Select data range (Category, Sales).
2. Insert → Pivot Table.
3. Drag 'Category' to Rows and 'Sales' to Values.
4. The table shows total sales for each category.
7. Command to install Matplotlib using pip:
pip install matplotlib
8. Importance of formatting charts in Excel:
Formatting enhances chart clarity and readability.
It helps highlight key insights and makes data visually appealing.
Example: Changing colors or font styles improves interpretation.
9. Change column chart to line chart:
Steps:
1. Select the chart.
2. Go to Chart Tools → Change Chart Type.
3. Choose Line Chart → OK.
10. Four types of charts in MS Excel:
Column, Pie, Line, Bar
11. Change plot area background color to light gray:
Steps:
1. Select Plot Area.
2. Right-click → Format Plot Area.
3. Choose Fill → Light Gray color.
12. Create a column chart for monthly sales data:
Steps:
1. Select monthly sales data.
2. Go to Insert → Charts → Column Chart.
3. Customize as needed.
Example: Shows total sales for each month.
13. Data markers in charts:
Data markers represent individual data points on a chart.
They help in identifying specific values.
Example: A dot showing monthly sales on a line chart.
14. Change font style and size of chart text:
Steps:
1. Click text element (title, axis labels).
2. Right-click → Font → Select style and size.
15. Apply stacked column chart:
Steps:
1. Select data.
2. Insert → Column → Stacked Column Chart.
3. Customize colors and labels.
16. Importance of formatting charts:
Same as Q8 — Improves clarity, focus, and presentation.
17. Explode a slice of pie chart:
When exploded, one slice separates from the pie to highlight it.
Example: Showing focus on highest sales region.
18. Steps to create a simple chart:
1. Select data.
2. Insert → Chart type.
3. Customize title, labels, and colors.
19. Function to draw a line plot:
[Link]()
20. Syntax for bar chart:
[Link](x, y)
21. Function for scatter plot:
[Link](x, y)
22. Argument to set figure size:
figsize=(width, height)
23. Bar chart program:
import [Link] as plt
[Link](['A','B','C'], [5,7,3])
[Link](['Products'])
[Link]()
24. Save line chart as [Link] and [Link]:
[Link]('[Link]', dpi=300)
[Link]('[Link]', dpi=300)
25. Define aspect ratio:
Aspect ratio is the ratio between width and height of a figure.
Example: figsize=(8,4) means width twice height.
26. Change figure size to 8x6:
[Link](figsize=(8,6))
27. Use of [Link]() and [Link]():
Used to set range of x and y axes.
Example:
[Link](0,10)
[Link](0,50)
28. Role of Matplotlib in data visualization:
Matplotlib helps visualize data using graphs and charts.
Setup:
1. pip install matplotlib
2. import [Link] as plt
3. Create plot using [Link]()
29. Simple line plot program:
import [Link] as plt
x=[1,2,3,4,5]
y=[2,4,6,8,10]
[Link](x,y,label='Data')
[Link]('Simple Line Plot')
[Link]('X-Axis')
[Link]('Y-Axis')
[Link]()
[Link]()
30. Adjust resolution of saved plot:
Use dpi in savefig() to control quality.
Example: [Link]('[Link]', dpi=300)
Higher dpi = better resolution.