IT- Skill Lab – 2 (KMBN251)

MASTER OF BUSINESS ADMINISTRATION

BATCH : 2020-2022

(Dr. Abdul Kalam Technical University, Lucknow, Uttar Pradesh)

Submitted To: Submitted By: SHRUTI

Prof. R.K TOMER

Roll No: 2002160700091

INDEX
01
S.no. TITLE Page
No.
1. Developing pivot table 03

2. Pivot Table: Developing Pivot Table, Analyzing data using goal seek and solver, 04
Scenarios Create named scenarios. Show, edit, delete scenarios, Creating a scenario
summary report.

3. Validating and Auditing: Set, edit validation criteria for data entry in a cell range like: 14
whole number, decimal, list, date, time, Trace precedent, dependent cells.

4. Identify cells with missing dependents 15

5. Creating applications in Spreadsheet and Macros. 18

6. Creating and formatting Charts: Understanding chart types, column chart, bar chart, 22
line chart, pie chart, XY Scatter chart , Area chart, surface chart, bubble chart.

7. Change the chart type for a defined data series, Add, delete a data series in a chart, 32
Re-position chart title, legend, data labels.

8. Change scale of value axis: minimum, maximum number to display, major interval. 36

9. Change display units on value axis without changing data source: hundreds, 39
thousands,millions

10. Format columns, bars, pie slices, plot area, chart area to display an image. 42

Developing pivot table:-

02
Pivot tables extract meaning from that seemingly endless jumble of numbers on your screen. And more specifically, it
lets you group your data together in different ways so you can draw helpful conclusions more easily.

The "pivot" part of a pivot table stems from the fact that you can rotate (or pivot) the data in the table in order to view it
from a different perspective. To be clear, you're not adding to, subtracting from, or otherwise changing your data when
you make a pivot. Instead, you're simply reorganizing the data so you can reveal useful information from it.

The pivot table is one of Microsoft Excel's most powerful -- and intimidating -- functions. Powerful because it can help
you summarize and make sense of large data sets. Intimidating because you're not exactly an Excel expert, and pivot
tables have always had a reputation for being complicated.

The good news: Learning how to create a pivot table in Excel is much easier than you might've been led to believe.

But before we walk you through process of creating one, let's take a step back and make sure you understand exactly
what a pivot table is, and why you might need to use one.

Steps to create pivot table:-

1. Enter your data into a range of rows and columns.

2. Sort your data by a specific attribute.
3. Highlight your cells to create your pivot table.
4. Drag and drop a field into the “Row Labels” area.
5. Drag and drop a field into the “Values” area.
6. Fine-tune your calculations.

03
Analyzing data using goal seek and solver:

Goal Seek is Excel's built-in What-If Analysis tool that shows how one value in a formula impacts another. More
precisely, it determines what value you should enter in an input cell to get the desired result in a formula cell.

The best thing about Excel Goal Seek is that it performs all calculations behind the scenes, and you are only asked to
specify these three parameters:

Formula cell

Target/desired value

The cell to change in order to achieve the target

The Goal Seek tool is especially useful for doing sensitivity analysis in financial modeling and is widely used by
management majors and business owner. But there are many other uses that may prove helpful to you.

For instance, Goal Seek can tell you how much sales you have to make in a certain period to reach $100,000 annual net
profit (example 1). Or, what score you must achieve for your last exam to receive an overall passing score of 70%
(example 2). Or, how many votes you need to get in order to win the election (example 3).

04
On the whole, whenever you want a formula to return a specific result but are not sure what input value within the
formula to adjust to get that result, stop guessing and use the Excel Goal Seek function!

Scenarios create named scenarios:-

To create your scenarios with the Scenario Manager, follow these steps:

1. the changing cells in the spreadsheet; that is, the cells whose values vary in each of your scenarios.
Remember that you can select nonadjacent cells in the worksheet by holding down the Ctrl key as you click
them.

2. the What-If Analysis command button on the Ribbon’s Data tab and then click Scenario Manager on its drop-
down menu or press Alt+AWS.
This action opens the Scenario Manager dialog box.

3. Click the Add button in the Scenario Manager dialog box.

This action opens the Add Scenario dialog box, similar to the one shown. The Add Scenario dialog box contains a
Scenario Name text box, where you give the new scenario a descriptive name such as Best Case, Most Likely
Case, and so on. This dialog box also contains a Changing Cells text box that contains the addresses of the
variable cells that you selected in the worksheet, a Comment box that contains a note with your name and the
current date, so you’ll always know when you created the particular scenario, and Protection check boxes that
prevent users from making changes and/or enable you to hide the scenario when the worksheet is protected.

05
4. Type a descriptive name for the new scenario in the Scenario Name text box.
Now, you should check over the cell references in the Changing Cells text box to make sure that they’re correct.
You can modify them if necessary by clicking the Changing Cells text box and then by clicking the cells in the
worksheet while holding down the Ctrl key. You can also edit the note in the Comment box if you want to add
more information about your assumptions as part of the new scenario.By default, Excel protects a scenario
from changes when you turn on protection for the worksheet so that you can’t edit or delete the scenario in
any way. If you want Excel to hide the scenario as well when worksheet protection is turned on, click the Hide
check box. If you don’t want to protect or hide the scenario when worksheet protection is turned on, click the
Prevent Changes check box to remove its check mark, and leave the Hide check box as it is.

5. In the Protection section of the Add Scenario dialog box, choose what kind of scenario protection that you need,
if any, with the Prevent Changes and Hide check boxes.
Now you’re ready to specify the changing values for the new scenario.

6. Click OK in the Add Scenario dialog box.

This action closes the Add Scenario dialog box and then opens the Scenario Values dialog box, similar to the one
shown. The Scenario Values dialog box numbers and shows the range name (assuming that you named each of
the cells), followed by the current value for each of the changing values that you selected in the worksheet
before starting to define different scenarios for your spreadsheet.

06
7. Check the values in each changing cell’s text box and modify the values as needed.
Now you’re ready to close the Scenario Values dialog box, which completes the definition of the new scenario.
8. Click the Add button in the Scenario Values dialog box.
This action closes the Scenario Values dialog box and returns you to the Add Scenario dialog box, where you can
define a new scenario name for the changing cells.

9. Repeat Steps 4 to 7 to add all the other scenarios that you want to create.
After you finish defining all the different scenarios you want to apply to the changing values in the spreadsheet,
you can close the Scenario Values dialog box and then return to the Scenario Manager dialog box, where you
can use the Show button to see how using different sets of changing values affects your spreadsheet.

10. Click OK in the Add Values dialog box and then click the Close button in the Scenario Manager dialog box.
When you return to the Scenario Manager dialog box, the names of all the scenarios that you added appear in
the Scenarios list box. For example, in the figure, you see that three scenarios — Most Likely, Best Case, and
Worst Case — are now listed in the Scenarios list box.
To show a particular scenario in the worksheet that uses the values you entered for the changing cells, you
simply double-click the scenario name in this list box or click the name and then click the Show command
button. The figure shows the results in the sample forecast worksheet after showing the Worst Case scenario.

07
Edit a Scenario

1. Choose Scenarios from the Tools menu. Excel displays the Scenario Manager.
2. Highlight the name of the scenario whose values you want to change.
3. Click on the Edit button. Excel displays the Edit Scenario dialog box. (See Figure 1.)

4. Change the name of the scenario, cell range it refers to, or any comments, as desired

08
 5. Click on the OK button. Excel displays the Scenario Values dialog box. (See Figure 2.)

6. Change the values stored in the scenario, as desired.

7. Click on the OK button. Your changes are saved and the Scenario Manager is again displayed.
8. Repeat steps 2 through 7 for any other scenarios you want to change.
9. Click on Close to dismiss the Scenario Manager.

Delete scenario:

1. Navigate to the scenario type tab with the required scenario.

2. Right-click the required scenario and select Delete from the context menu.

A confirmation message will pop up.

3. Click Yes to delete scenario and its experiment results, if any.

To delete several scenarios

09
 1. remove more than one scenario, select the required scenarios by doing either of the following:

Hold Ctrl and click the items that you need to remove. Release the Ctrl button when selecting is done.

In case of a succession of items, click the first item to be deleted and hold the Shift key while clicking the last item to be
deleted.

2. -click one of the selected items and selectDelete from the context menu.

Creating a scenario summary report:

1. Select the cells that contain values that could change.

2. Click the Data tab on the ribbon.
3. Click the What-If Analysis button.
4. Select Scenario Manager.The Scenario Manager dialog box appears with the message “No Scenarios defined.
Choose Add to add scenarios.
5. Click the Add button to add a new scenario.

6. Type a name for the new scenario

If you already have cells selected, the Changing cells field will already be populated with your selection. If you
didn’t select cells up front, you’ll have to specify the cells here.

010
7. Click OK.

The Scenario Values dialog box appears, showing each of the variable cells you selected.
Tips: you name the worksheet cells you're changing, the cell names appear here, making it easy to tell what
value you're working with.
8. Update any values you want to see for the given scenario.
Tips: make sure you don’t lose the original values for the changing cells, use the original cell values in the first
scenario you create.

011
9. Click OK

The scenario is added and is listed in the Scenario Manager. If you click Add, the Add Scenario dialog box
appears again so you can add another scenario.
Repeat steps 5-9 to add all the desired scenarios.
10. Select the scenario you want to view
11. Click the Show button

012
The worksheet’s values are changed to the values you specified in the scenario. You can select any scenario here
to update the values in your spreadsheet.

013
Validating and Auditing:Set Data validation in Excel is an existing and accessible tool to ensure data integrity
while building a financial model. Although it is only an easy extra step, data validation in Excel is an extremely useful tool
to secure your model inputs and outputs. This article is intended to provide readers with a better understanding of why
and how to use data validation, in simple and easy steps.

Example of Data Validation in Excel:

In this example, we will use data validation to ensure that the gross margin stays within the range of 0 to 0.9.

In Excel, data validation is found on the “Data” ribbon, under “Data Validation.” Once clicked, the data validation
window will pop up. It will show three tabs: Settings, Input Message, and Error Alert.

In this screenshot, we can see that the “GrossMargin” input range has been set to between 0 and 90 percent.
Although you cannot see the “Error Alert” tab, it has been set to alert users with a warning message if the
“GrossMargin” input has been set outside of the range. This is a useful example, as gross margins are always
between 0 and 1; however, it is extremely rare that gross margins exceed 90% of sales. In order to ensure data
integrity and eliminate the possibility of gross margin exceeding 90% of sales, data validation has been used.

014
Edit validation Criteria for data entry In a cell range:

1. Select the cell(s) you want to create a rule for.

2. Select Data >Data Validation.

3. On the Settings tab, under Allow, select an option:

 Whole Number - to restrict the cell to accept only whole numbers.
 Decimal - to restrict the cell to accept only decimal numbers.
 List - to pick data from the drop-down list.
 Date - to restrict the cell to accept only date.
 Time - to restrict the cell to accept only time.
 Text Length - to restrict the length of the text.
 Custom – for custom formula

4. Under Data, select a condition.

5. Set the other required values based on what you chose for Allow and Data.
6. Select the Input Message tab and customize a message users will see when entering data.
7. Select the Show input message when cell is selected checkbox to display the message when the user selects
or hovers over the selected cell(s).
8. Select the Error Alert tab to customize the error message and to choose a Style.
9. Select OK.
10. Now, if the user tries to enter a value that is not valid, an Error Alert appears with your customized message.

How to do data validation in Excel:

To add data validation in Excel, perform the following steps

1. Open the Data Validation dialog box

Select one or more cells to validate, go to the Data tab > Data Tools group, and click the Data Validation
button.

You can also open the Data Validation dialog box by pressing Alt > D > L, with each key pressed separately

015
 2. Create an Excel validation rule
On the Settings tab, define the validation criteria according to your needs. In the criteria, you can supply any
of the following:
 Values - type numbers in the criteria boxes like shown in the screenshot below.
 Cell references - make a rule based on a value or formula in another cell.
 Formulas - allow to express more complex conditions like in this example.
As an example, let's make a rule that restricts users to entering a whole number between 1000 and 9999:

With the validation rule configured, either click OK to close the Data Validation window or switch to another tab to add
an input message or/and error alert.

016
 3. Add an input
message (optional)
If you want to display a message that explains to the user what data is allowed in a given cell, open the Input
Message tab and do the following:
 Make sure the Show input message when cell is selected box is checked.
 Enter the title and text of your message into the corresponding fields.
 Click OK to close the dialog window.

As soon as the user selects the validated cell, the following message will show up:

3. Display an error alert (optional):

In addition to the input message, you can show one of the following error alerts when invalid data is entered
in a cell

017
018
To configure a custom error message, go to the Error Alert tab and define the following parameters:

 Check the Show error alert after invalid data is entered box (usually selected by default).
 In the Style box, select the desired alert type.
 Enter the title and text of the error message into the corresponding boxes.
 Click OK.

And now, if the user enters invalid data, Excel will display a special alert explaining the error (like shown in the beginning
of this tutorial).

Note. If you do not type your own message, the default Stop alert with the following text will show up: This value does
not match the data validation restrictions defined for this cell.

Whole numbers and decimals:

To restrict data entry to a whole number or decimal, select the corresponding item in the Allow box. And then, choose
one of the following criteria in the Data box:

 Equal to or not equal to the specified number

 Greater than or less than the specified number
 Between the two numbers or not between to exclude that range of numbers

For example, this is how you create an Excel validation rule that allows any whole number greater than 0:

019
Date and time validation in Excel:

To validate dates, select Date in the Allow box, and then pick an appropriate criteria in the Data box. There are quite a
lot of predefined options to choose from: allow only dates between two dates, equal to, greater than or less than a
specific date, and more.

Similarly, to validate times, select Time in the Allow box, and then define the required criteria.

For example, to allow only dates between Start date in B1 and End date in B2, apply this Excel date validation rule :

To validate entries based on today's data and current time, make your own data validation formulas as shown in these
examples:

Validate dates based on today's date

Validate times based on current time

020
Text length:

To allow data entry of a specific length, select Text length in the Allow box, and choose the validation criteria in
accordance with your business logic.

For example, to limit the input to 10 characters, create this rule:

Understanding chart types :

Column chart:
Data that’s arranged in columns or rows on a worksheet can be plotted in a column chart. A column chart
typically displays categories along the horizontal (category) axis and values along the vertical (value) axis, as
shown in this chart:

021
Types of column charts:-

 Clustered column and 3-D clustered column

A clustered column chart shows values in 2-D columns. A 3-D clustered column chart shows columns in
3-D format, but it doesn’t use a third value axis (depth axis). Use this chart when you have categories
that represent:
1. Ranges of values (for example, item counts).
2. Specific scale arrangements (for example, a Likert scale with entries like Strongly agree, Agree,
Neutral, Disagree, Strongly disagree).
3. Names that are not in any specific order (for example, item names, geographic names, or the
names of people).
 Stacked column and 3-D stacked column

A stacked column chart shows values in 2-D stacked columns. A 3-D stacked column chart shows the
stacked columns in 3-D format, but it doesn’t use a depth axis. Use this chart when you have multiple
data series and you want to emphasize the total.
 100% stacked column and 3-D 100% stacked column
A 100% stacked column chart shows values in 2-D columns that are stacked to represent 100%. A 3-D
100% stacked column chart shows the columns in 3-D format, but it doesn’t use a depth axis. Use this
chart when you have two or more data series and you want to emphasize the contributions to the
whole, especially if the total is the same for each category.

 3-D column
3-D column charts use three axes that you can change (a horizontal axis, a vertical axis, and a depth
axis), and they compare data points along the horizontal and the depth axes. Use this chart when you
want to compare data across both categories and data series.

022
Line chart:-

Data that's arranged in columns or rows on a worksheet can be plotted in a line chart. In a line chart, category
data is distributed evenly along the horizontal axis, and all value data is distributed evenly along the vertical
axis. Line charts can show continuous data over time on an evenly scaled axis, so they're ideal for showing
trends in data at equal intervals, like months, quarters, or fiscal years.

Types of line charts:-

 Line and line with markers

Shown with or without markers to indicate individual data values, line charts can show trends over
time or evenly spaced categories, especially when you have many data points and the order in which
they are presented is important. If there are many categories or the values are approximate, use a line
chart without markers.

 Stacked line and stacked line with markers

023
 Shown with or without markers to indicate individual data values, stacked line charts can show the
trend of the contribution of each value over time or evenly spaced categories.

 100% stacked line and 100% stacked line with markers

Shown with or without markers to indicate individual data values, 100% stacked line charts can show
the trend of the percentage each value contributes over time or evenly spaced categories. If there are
many categories or the values are approximate, use a 100% stacked line chart without markers.

 3-D line
3-D line charts show each row or column of data as a 3-D ribbon. A 3-D line chart has horizontal,
vertical, and depth axes that you can change.

Pie Chart:-
Data that's arranged in one column or row on a worksheet can be plotted in a pie chart. Pie charts show the
size of items in one data series, proportional to the sum of the items. The data points in a pie chart are shown
as a percentage of the whole pie.

024
Consider using a pie chart when:

 You have only one data series.

 None of the values in your data are negative.
 Almost none of the values in your data are zero values.
 You have no more than seven categories, all of which represent parts of the whole pie.
Types of pie charts:-

 Pie and 3-D pie

Pie charts show the contribution of each value to a total in a 2-D or 3-D format. You can pull out
slices of a pie chart manually to emphasize the slices.

 Pie of pie and bar of pie

Pie of pie or bar of pie charts show pie charts with smaller values pulled out into a secondary pie or
stacked bar chart, which makes them easier to distinguish.

Bar chart

025
Data that's arranged in columns or rows on a worksheet can be plotted in a bar chart. Bar charts illustrate
comparisons among individual items. In a bar chart, the categories are typically organized along the vertical
axis, and the values along the horizontal axis.

Consider using a bar chart when:

 The axis labels are long.

 The values that are shown are durations.
Types of bar charts:-

 Clustered bar and 3-D clustered bar

A clustered bar chart shows bars in 2-D format. A 3-D clustered bar chart shows bars in 3-D format; it
doesn’t use a depth axis.

 Stacked bar and 3-D stacked bar

Stacked bar charts show the relationship of individual items to the whole in 2-D bars. A 3-D stacked bar
chart shows bars in 3-D format; it doesn’t use a depth axis.

 100% stacked bar and 3-D 100% stacked bar

A 100% stacked bar shows 2-D bars that compare the percentage that each value contributes to a total
across categories. A 3-D 100% stacked bar chart shows bars in 3-D format; it doesn’t use a depth axis.

026
Area chart
Data that's arranged in columns or rows on a worksheet can be plotted in an area chart. Area charts can be
used to plot change over time and draw attention to the total value across a trend. By showing the sum of the
plotted values, an area chart also shows the relationship of parts to a whole.

Types of area charts:-

 Area and 3-D area

Shown in 2-D or in 3-D format, area charts show the trend of values over time or other category data.
3-D area charts use three axes (horizontal, vertical, and depth) that you can change. As a rule, consider
using a line chart instead of a non-stacked area chart, because data from one series can be hidden
behind data from another series.

 Stacked area and 3-D stacked area

Stacked area charts show the trend of the contribution of each value over time or other category data
in 2-D format. A 3-D stacked area chart does the same, but it shows areas in 3-D format without using a
depth axis.

 100% stacked area and 3-D 100% stacked area

027
 100% stacked area charts show the trend of the percentage that each value contributes over time or
other category data. A 3-D 100% stacked area chart does the same, but it shows areas in 3-D format
without using a depth axis.

Bubble chart
Much like a scatter chart, a bubble chart adds a third column to specify the size of the bubbles it shows to
represent the data points in the data series

Type of bubble:-

 Bubble or bubble with 3-D effect

Both of these bubble charts compare sets of three values instead of two, showing bubbles in 2-D or 3-D
format (without using a depth axis). The third value specifies the size of the bubble marker.

028
Surface chart
Data that's arranged in columns or rows on a worksheet can be plotted in a surface chart. This chart is useful
when you want to find optimum combinations between two sets of data. As in a topographic map, colors and
patterns indicate areas that are in the same range of values. You can create a surface chart when both
categories and data series are numeric values.

Types of surface charts:-

 3-D surface
This chart shows a 3-D view of the data, which can be imagined as a rubber sheet stretched over a 3-D
column chart. It is typically used to show relationships between large amounts of data that may
otherwise be difficult to see. Color bands in a surface chart do not represent the data series; they
indicate the difference between the values.

 Wireframe 3-D surface

Shown without color on the surface, a 3-D surface chart is called a wireframe 3-D surface chart. This
chart shows only the lines. A wireframe 3-D surface chart isn’t easy to read, but it can plot large data
sets much faster than a 3-D surface chart.

029
  Contour
Contour charts are surface charts viewed from above, similar to 2-D topographic maps. In a contour
chart, color bands represent specific ranges of values. The lines in a contour chart connect interpolated
points of equal value.

 Wireframe contour
Wireframe contour charts are also surface charts viewed from above. Without color bands on the
surface, a wireframe chart shows only the lines. Wireframe contour charts aren’t easy to read. You
may want to use a 3-D surface chart instead.

XY scatter chart
Data that's arranged in columns and rows on a worksheet can be plotted in an xy (scatter) chart. Place the x
values in one row or column, and then enter the corresponding y values in the adjacent rows or columns.

A scatter chart has two value axes: a horizontal (x) and a vertical (y) value axis. It combines x and y values into
single data points and shows them in irregular intervals, or clusters. Scatter charts are typically used for
showing and comparing numeric values, like scientific, statistical, and engineering data.

Consider using a scatter chart when:

 You want to change the scale of the horizontal axis.

030
  You want to make that axis a logarithmic scale.
 Values for horizontal axis are not evenly spaced.
 There are many data points on the horizontal axis.
 You want to adjust the independent axis scales of a scatter chart to reveal more information about
data that includes pairs or grouped sets of values.
 You want to show similarities between large sets of data instead of differences between data points.
 You want to compare many data points without regard to time—the more data that you include in a
scatter chart, the better the comparisons you can make.

Types of scatter charts

 Scatter
This chart shows data points without connecting lines to compare pairs of values.

 Scatter with smooth lines and markers and scatter with smooth lines
This chart shows a smooth curve that connects the data points. Smooth lines can be shown with or
without markers. Use a smooth line without markers if there are many data points.

 Scatter with straight lines and markers and scatter with straight lines
This chart shows straight connecting lines between data points. Straight lines can be shown with or
without markers.

Change a Series Chart Type

To make the Cases series stand out, we'll change it to a line series, and later we'll plot it on a separate axis.

In this example, the Cases series is difficult to see, so you can use the Ribbon commands to select it.
To select a specific series:
1. On the worksheet, click on the chart to select it.

031
2. On the Ribbon, click the Layout tab, under Chart Tools
3. At the left end of the Ribbon, in the Current Selection group, click the drop down arrow
4. Series "Cases" to select that series

To change a series chart type:

1. In the chart, right-click on one of the selected Cases columns.
2. In the shortcut menu that appears, click Change Series Chart Type
3. In the Change Chart Type window, click on the Line category, and click the first Line chart
Subtype

4. Click OK to close the Change Chart Type window.

5. The Cases series changes to a Line chart type, but it is still hard to see, because the numbers
are so small. Next, we'll change its axis.

032
Delete a data series in the chart:-

Click the chart.

Click the Design tab under Chart Tools, and then click the Select Data button

Click the data series you want to delete.

Click Remove.

033
Advanced Spreadsheets – Re-position chart title, legend, data labels:-
To re-position the chart title or legends in a Calc chart, first of all select the chart. After this, click once on the
chart title or the legends box, the chart title or the legend box will get selected. Now without releasing the
mouse button drag the title or the legends to the position where you want to place them. Release the mouse
button once the appropriate location is reached and then release the mouse button. The chart title or the
legend will get re-positioned.
To re-position the data labels, right-click on the label which you want to re-position and from the resulting
menu, click on ‘Format Data Labels’. This will open the ‘Data Labels’ dialog box.

034
In this dialog box, under the ‘Data Labels’ tab, we have a ‘Placement’ drop down list. The appropriate position
for the data label can be selected from this list. After selecting the position, click on ‘OK’. This will re-position
the data label.

Changing the Axis Scale:-

Excel includes an impressive graphing capability that can turn the dullest data into outstanding charts,
complete with all sorts of whiz-bang do-dads to amaze your friends and confound your enemies. While Excel
035
can automatically handle many of the mundane tasks associated with turning raw data into a chart, you may
still want to change some elements of your chart.

For instance, you may want to change the scale Excel uses along an axis of your chart. (The scale automatically
chosen by Excel may not represent the entire universe of possibilities you want conveyed in your chart.) You
can change the scale used by Excel by following these steps in Excel 2007 or Excel 2010:
1. Right-click on the axis whose scale you want to change. Excel displays a Context menu for the axis.
2. Choose Format Axis from the Context menu. (If there is no Format Axis choice, then you did not
right-click on an axis in step 1.) Excel displays the Format Axis dialog box.
3. Make sure Axis Options is clicked at the left of the dialog box. (See Figure 1.)

4. the scale settings (top of the dialog box—Minimum, Maximum, etc.) as desired.
5. Click on OK.
In Excel 2013 and later versions, the steps are different:

036
1. Right-click on the axis whose scale you want to change. Excel displays a Context menu for the axis.
2. Choose Format Axis from the Context menu. (If there is no Format Axis choice, then you did not
right-click on an axis in step 1.) Excel displays the Format Axis task pane at the right side of the
screen.
3. Make sure Axis Options area is expanded. (Click on Axis Options and then the Axis Options icon.)
(See Figure 2.)

4. Adjust the Bounds and Units settings, as desired. These, along with a couple of other settings in the
task pane, determine the scale used to display your chart.
5. Close the Format Axis task pane.

037
Note that in order to adjust the Bounds and Units settings, Excel needs to recognize the data in an axis as a
range of values (e.g. dates). There will not be the option to change Bounds and Units if the data is recognized
as discreet values by Excel (e.g. item names).
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9267) applies to Microsoft Excel
2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu
interface of Excel here: Changing the Axis Scale.

Change display unit without changing data source:-

038
You can go to the Format Axis dialog to format the axis.
1. Right click at the axis you want to format its labels as thousands/millions, select Format Axis in the
context menu.

2. In the Format Axis dialog/pane, click Number tab, then in the Category list box, select Custom, and
type [>999999] #,,"M";#,"K" into Format Code text box, and click Add button to add it to Type list.
See screenshot:

039
3. Close dialog, now you can see the axis labels are formatted as thousands or millions.

Modifying Axis Scale Labels:

It is very common for charts to use some sort of "shorthand" for values placed along an axis. For
instance, if the values along an axis ranged from 0 to 80,000, you may want to have only the
thousands portion of each value displayed on the axis. That way, instead of 20,000, 40,000, 60,000,

040
and 80,000, you would see 20, 40, 60, and 80 along the axis. A note could then be made in a label
that indicates the axis values are displayed in thousands.

You can very easily change the axis scale by simply modifying how the values on the axis are
displayed. Follow these steps:

1. Create your chart as you normally would.

2. Double-click the axis you want to scale. You should see the Format Axis dialog box. (If double-
clicking doesn't work, right-click the axis and choose Format Axis from the resulting Context
menu.)
3. Make sure the Number tab is displayed. (See Figure 1.)

4. In the Category list, choose Custom.

5. In the Type box, enter a zero followed by a comma.
6. Click OK.
Only the thousands portion of the values in the axis should be displayed. You can then add another
label, as desired, that indicates the values are expressed in thousands. If you'd prefer to not add
the additional label, you can always use a format of "0,K" (without the quote marks) in step 5.
A different way to approach the problem is with these steps, which works in Excel 2000, Excel 2002,
and Excel 2003:
1. Create your chart as you normally would.
2. Double-click the axis you want to scale. You should see the Format Axis dialog box. (If
double-clicking doesn't work, right-click the axis and choose Format Axis from the resulting
Context menu.)

041
 3. Make sure the Scale tab is displayed. (See Figure 2.)

4. Using the Display Units drop-down list, choose Thousands.

5. Click OK.
Excel changes the axis values so only the thousands portion is displayed, and inserts a label saying
Thousands. Double-click on the Thousands label to edit the label, as desired, then drag it to any desired
position.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3180) applies to Microsoft
Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel
2007 and later) here: Modifying Axis Scale Labels.

042
Format:-

COLUMN CHARTS
As an example consider categorical data on 1997 U.S. Health Care Expenditures.

The data are in file

To create a column chart from the above

 Highlight the data and headings (here columns A1:B13)
 Select the Insert Tab and Charts group and click on Column Chart
 Select the first of the 2-D Column Charts (a clustered column chart).

043
This yields
Note that Excel uses the first series (Category) for the x-axis labels and the second series (Expenditures)
for the y-axis values.
Also note the advantage to having the original data ordered in descending value of expenditures.

Chart:-
Select the chart element (for example, data series, axes, or titles), right-click it, and click Format <chart
element>. The Format pane appears with options that are tailored for the selected chart element.

Clicking the small icons at the top of the pane moves you to other parts of the pane with more options.
If you click on a different chart element, you’ll see that the task pane automatically updates to the new
chart element.

Pie:-
A Pie Chart displays one series of data. A data series is a row or column of numbers used for charting.
In the worksheet below, we have outlined a single data series. If we had selected multiple series for
the Pie Chart, Excel would ignore all but the first.

044
Excel uses the row heading (series identifier) for the chart title and displays the data as proportional
slices of a pie (1st image below). One can customize the design of the pie chart so either numeric
values or their percentages display on top of the slices of pie.

The legend can be moved to the top, bottom, left, right, or top right ("corner" in older versions) of the
chart. Legend names can be changed by changing the column headings in the sheet or editing the chart
directly in new Excel versions.

Popular Pie Chart sub-types include Pie Chart in 3-D (2nd chart above), Exploded Pie Chart (3rd chart
above), and Exploded Pie in 3-D. Other sub-types include the Pie of Pie and Bar of Pie - where a second
pie or bar is created from certain values in the first pie. To customize, right-click on the segment in the
first pie and select "Format Data Point."

045
046