Kashi Institute of Technology
Varanasi
Affiliated to
[Dr. A.P.J. ABDUL KALAM TECHNICAL UNIVERSITY
LUCKNOW (AKTU) ]
Department
of
Management
Digital Notes on IT SKILL-2 (BMB-251)
Course – MBA
Created and Design By: -
Asst. Prof. Deepak Kumar Benbanshi
(KIT VNS)
UNIT-1
Ex cel- Summarise and Analys e Data w i th P iv ot Tables
Pivot Tables are one of Excel’s best features. You can use a Pivot Table to summarise and analyse data. It is
particularly useful if you work with large amounts of data and need a quick, flexible way to produce reports
from it.
Make sure your data is well laid-out
In order to create a Pivot Table successfully, just follow a few golden rules:
Your data should be arranged in columns and rows.
Each column should contain a descriptive heading.
Although not essential, it’s good practice to apply
formatting, e.g. bold to the column headings.
Avoid empty rows or columns within the data (an occasional empty cell is OK).
Turning your data into an Excel Table will make it easier to add or remove rows and columns in your
data without having to redefine the data range of the Pivot Table.
o Click a cell within your block of data.
o Press CTRL + T on the keyboard, or click the Insert tab on the ribbon and click
Table.
Use the Table Tools on the ribbon to apply a Table Style to your data.
Create the Pivot Table
1. Click the Insert tab
2. Click Pivot Table on the ribbon or press Alt + N+V or click Recommended Pivot
Tables
Choose the information to display in the Pivot Table
Excel creates the pivot table and places a set of Pivot table Tools on the ribbon. Use these to work with
your pivot table.
Excel also displays the Pivot Table Fields area at the right-hand side of the screen. This is where you can
construct the pivot table and decide on its layout.
The pivot table includes totals by default, but you can choose whether to include these or not- Use the
Grand Totals button in the Layout section of the Design tab of the PivotTable Tools.
Kashi Institute of Technology Varanasi
Design the pivot table
Designing your pivot table is as simple as dragging fields from the field list to the field areas. As you do,
Excel builds the pivot table on the worksheet so you can see how the data will look.
See your data in a different way
Pivot Tables are extremely flexible so if you want to change how your data is arranged, just click and drag
the fields from one area of the field list pane to another. Excel will rebuild the pivot table and rearrange
the data.
If you no longer want a field as part of the pivot table, drag it out of the field list area until you
see a cross attached to your mouse then release the mouse button, or click the tick against the
field name in the Field List at the right of the widow to deselect it.
2
Change the calculation used to summarise your data
1. Right-click a summarised value in the pivot table.
2. Choose Summarize Values By from the shortcut menu to display a
list of available functions.
3. Click the function you want to use to recalculate your data.
Note: by default Excel summarises numeric fields in pivot tables by Sum
and text data by Count.
Refresh the pivot table
If you change any of the source data for your pivot table, the pivot table will not recalculate automatically.
To reflect any changes you have made to the source data, you must refresh the pivot
table.
1. Make changes in the source data.
2. Click into the pivot table.
3. Right-click any cell in the pivot table, and choose Refresh from the shortcut menu.
Alternatively, click the Refresh button on the Analyze tab of the PivotTable Tools on the
ribbon.
Format the Pivot Table
Use the Tools on the Design tab to choose the look for the whole pivot table
Right-click a value in the pivot table and choose Number Format… from the shortcut
menu to select the number format you want.
Right-click text in the pivot table and choose Foramt Cells… from the shortcut menu to
set the text format you want.
Drill down into a summarised value to view details
1. Double-click a summarised value in the pivot table.
2. Excel creates a new sheet and places all the items that make up the summarised value into separate
rows.
Note: the data on the new sheet
is static, so won’t reflect any
later changes.
3
Filter a pivot table
You can filter the data in a pibot table in several ways:
Drag a field to the Filter area of the pivot table
Filter the Row labels and/or Column labels
Use the Filter arrows to choose the data to display
Use Slicers to select the data to display– see our guide Filtering a pivot table with slicers for more details
Group data in a pivot table
Sometimes you may want to group data instead of seeing it in distinct rows or columns. This is particularly
useful for dates. Excel can group dates automatically but you can also group data manually:
Click and drag the items you want to group
Right-click an item and click Group… from the shortcut menu. Depending on the data type, you may see
extra options, eg for dates you can can choose Years, Quarters, Months etc
4
A new column appears in the pivot table for the group total and a new group heading appears in the coumn
or row area, depending on how you’ve grouped the data.
Note: Excel uses generic names for any groups you create eg Group 1, but you can change this by selecting
the heading and overtyping with your own choice of title.
Once created, you can collapse/expand the group to display the level of detail you require
Click the outline symbol (- or +) alongside the Group label
Further information and help
Use MyIT to log calls with the IT Service Desk: [Link]
If you want to learn about any Microsoft product in more detail you can use Microsoft 365 help and
learning for tutorials, videos and helpful hints
5
UNIT-2
Creating and Formatting Charts in Microsoft Excel
Creating and Formatting Charts in Microsoft Excel
This document provides instructions for creating and formatting charts in Microsoft Excel,
which makes creating professional-looking charts easy. The chart type, chart layout, and chart
style are all within easy reach on the Ribbon. You will have instant professional results every
time you create a chart. And as the data changes in the datasheet, the chart will automatically
update to reflect the changes.
Creating a Chart
1. To create a basic chart in Excel that you can modify and format later, start by entering
the data for the chart on a worksheet. It is important to include titles in the datasheet so
that Excel can create legends and labels for the chart.
2. Then simply select that data (including titles) and press F11. This creates an instant
column chart in a new tab, which can be customized to meet your needs.
UNIT-2
Creating and Formatting Charts in Microsoft Excel
3. Or, instead of pressing F11, go to the Insert tab and select the type of chart you would
like to create.
4. This creates a chart embedded in the datasheet.
Formatting a Chart
Once a chart has been created, there are a multitude of formatting options that can be applied.
1. Select the chart. This activates the Chart Tools on the Ribbon.
2. Use the features available on the Design tab to:
a. Change the chart type.
b. Swap the rows and columns or adjust what data is charted. For example,
switching the columns and rows changes the look of the chart, even though the
UNIT-2
Creating and Formatting Charts in Microsoft Excel
raw data has not changed:
c. Apply a Chart Style.
d. Move the chart (from the datasheet to its own tab or vice versa).
3. Use the features available on the Layout tab to:
a. Insert a picture, shape, or text box.
b. Adjust the labels, including moving the legend, editing titles, and adding data
labels (like values).
c. Adjust the axes and gridlines (for example, showing numbers on the axis in
millions or adding or removing gridlines).
d. Format the background.
e. Add trendlines to forecast future data or error bars to show potential error
amounts.
4. Use the features available on the Format tab to:
a. Apply a style, fill, outline, or effect. (For example, to change the color of the
bars.)
b. Apply and format WordArt.
c. Use the dropdown list in the Current Selection group to quickly adjust which
area of the chart you want to format.
Working with Charts
1. Keep in mind that as data changes in the worksheet, the chart will automatically update.
UNIT-2
Creating and Formatting Charts in Microsoft Excel
2. You may want to name the tabs at the bottom of the workbook to ease in navigation.
a. To rename a tab, double-click its name.
b. Or right-click and choose Rename.
c. You can also right-click and choose Tab Color to change the color of the tabs.
3. There may be times when you want to chart summary data rather than individual
instances. For example, you may want to chart total expenditures for each quarter as
opposed to each monthly expenditure.
a. Use formulas to create the summary data.
b. Use the CTRL button on the keyboard to select non-adjacent data. Remember to
select titles so that Excel can appropriately label the chart.
After selecting one group
of cells, hold CTRL while
selecting additional
groups of cells.
UNIT-2
Creating and Formatting Charts in Microsoft Excel
Adding Excel Charts to Word or PowerPoint
Now that you have created the charts in Excel, you may want to add them to a Word document
or PowerPoint presentation. There are two options – inserting a static copy of the chart or
inserting a linked copy of the chart. A static copy does not change; it will NOT be updated if
the chart is updated in Excel. A linked copy will update if the chart is updated in Excel.
1. Select the chart in Excel.
2. From the Home tab, select Copy.
3. Open the document or presentation in which you want to insert the chart.
4. Place the cursor in the desired location.
5. To insert a static copy of the chart, select Paste from the Home tab.
A static copy will not reflect
changes made to the data in
UNIT-2
Creating and Formatting Charts in Microsoft Excel
6. To insert a linked copy of the chart, click the dropdown arrow underneath the Paste
button and choose Paste Special.
a. Select Microsoft Office Excel Chart Object.
b. Select Paste Link.
c. Click OK.
d. The chart in the document or presentation will update as the chart is updated in
Excel. Right-click on the chart and choose Update Link to force the application
to check for updates.
A linked copy will reflect changes
made to the data in Excel.
e. Note: To ensure that the entire chart is seen when pasted, before you click the
Copy button in Excel, go to the View tab, and click the 100% button to set the
Zoom level to 100%.
UNIT-2
Creating and Formatting Charts in Microsoft Excel
UNIT-2
Creating and Formatting Charts in Microsoft Excel
Types of Charts in Excel
Visualizing data is a key part of effective analysis, and Excel offers a wide range of tools
to help you present information clearly. Understanding the different types of charts in
Excel can help you choose the best way to display your data based on your goals and
audience. From simple visuals like bar charts and pie charts to more advanced options
such as scatter plots, histograms, and combo charts, each chart type serves a
specific purpose in data storytelling. In this guide, you’ll learn about the most commonly
used Excel chart types, their functions, and when to use each one to make your data
easier to interpret and more impactful.
Different Chart Types in Excel
There are various charts available in excel. Namely, they are:
Line Chart
Bar Chart
Column Chart
Area Chart
Pie Chart
Surface Chart
In this example, we will be using random car sales data, including model name and the
number of cars sold, as the dataset and represent it in the various graphs.
Create dataset
In this step, we will be inserting random financial sales data into our excel sheet. Below
is the screenshot of the random data we will use for our various graphs.
UNIT-2
Creating and Formatting Charts in Microsoft Excel
Fig. 1 -
Dataset
Line Chart
Line charts are most helpful in representing the trends. This can be very useful to
analyze the ups and downs in a range of data over a particular time span. The data
points in the chart are connected with the lines.
Note: To insert the graph, we need to select our dataset(or data table) and go to insert
and then in the chart section and insert whatever graph we want.
UNIT-2
Creating and Formatting Charts in Microsoft Excel
Fig. 2 -
Line Chart
Bar Chart
Bar charts are used to represent the categorical data using the rectangular horizontal
bars with their height and length proportional to the data values it is used to represent.
UNIT-2
Creating and Formatting Charts in Microsoft Excel
Fig. 3 - Bar
Chart
Column Chart
Column charts are used to represent the data in a vertical chart using the vertical bars.
These graphs are mostly used for comparing the data points in the data. Column chart
Fig. 4 -
UNIT-2
Creating and Formatting Charts in Microsoft Excel
Column Chart
Area Chart
Area charts are used to display graphically quantitative data. It is similar to the line chart
and based on it. The area between the lines is filled with color, and they are easy to
analyze as they are similar to the line chart showing ups and downs in the data.
Fig5 - Area
Chart
Pie Chart
Pie charts are circular statistical graphs that are divided into slices of pie in the
proportion to data values to represent the data. They are commonly used to analyze the
percentage allocation of data points incomplete dataset.
UNIT-2
Creating and Formatting Charts in Microsoft Excel
Fig6 - Pie
Chart
Surface Chart
Surface charts are 3-dimensional charts that are used to represent the data in a 3-
dimensional landscape. They are mainly used to represent the large dataset. They
display a variety of data at the same time.
UNIT-2
Creating and Formatting Charts in Microsoft Excel
Fig. 7 -
Surface Chart
Conclusion
Choosing the right chart type in Excel allows you to communicate your data more
effectively and highlight key insights. Whether you’re summarizing values with a column
chart, comparing trends with a line chart, or showing proportions with a pie chart,
Excel gives you flexible options to suit different needs. By understanding the strengths of
each Excel chart type, you’ll be better prepared to turn raw data into meaningful visuals
that support smarter decisions and clearer communication.