9/29/2020

The Basics Of Excel

• These common features includes assumed prior

knowledge. The purpose of these notes is for students
who are missing some of the basics.
• It also includes some useful features that you may not
be familiar with but because of time constraints they
won’t be covered in teaching tutorial.

MS-Excel tutorial notes by James Tam

Audience/Purpose

• These resources are meant to show a beginner the common

features of Excel.
• The focus is showing one way that features may be run rather
than distracting the reader and showing all possible ways.
– Learning multiple methods may shift the focus from using a useful
feature to memorizing all the ways in which that feature may be run.
• Because of the rudimentary nature of the material, the focus is
on how to run features rather than why use them (it should be
self evident why one would want to save a document).

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 1

 9/29/2020

Starting Excel: Invoking The Start Menu

• The process is similar to the Word tutorial (or starting any

program in Windows).
• Click on the Start Button (the exact appearance may vary
depending upon the version of Windows that you are running).

MS-Excel tutorial notes by James Tam

Starting Excel: Running Excel

• With the Start button clicked type in “Excel” (the name of the
program you wish to run).
• (There are many ways of starting a program in Windows, this is
an example where there is no “one right way” to do things).

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 2

 9/29/2020

Templates

• Pre-created spreadsheets for many types of problems

MS-Excel tutorial notes by James Tam

Example Template

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 3

 9/29/2020

Terminology

• A typical file produced by Word is referred to as a ‘document’.

• A typical file produced by a spreadsheet program such as Excel
or Google docs is often described as a ‘spreadsheet’.
• Be aware that Microsoft uses it’s own terminology.
– A spreadsheet (file produced by a spreadsheet program such as Excel) is
described as a ‘workbook’ by Microsoft.

MS-Excel tutorial notes by James Tam

Creating A New Blank Worksheet

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 4

 9/29/2020

The Excel Ribbon

• Similar to Word, the features of Excel can be accessed via the

ribbon.
• The tabs differ slightly between Office 2016 and 2019 (Draw)
– Office 2016:

– Office 2019:

MS-Excel tutorial notes by James Tam

Quick Summary Of Features In Each Tab Of The

Ribbon
• File
– Operations on an Excel spreadsheet (a spreadsheet ‘file’): save, save-
as, print etc.
• Home
– Many commonly used features: copy-cut-paste, formatting text, cell
alignment and even more advanced formatting features such as
“conditional formatting”.
• Insert
– Charts, pictures, shapes, symbols, pivot tables and more.
• Page Layout
– For configuring Excel when printing but other features, such as setting a
background image, for the spreadsheet can be done here.

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 5

 9/29/2020

Quick Summary Of Features In Each Tab Of The

Ribbon (2)
• Formulas
– Groups all the built-in formulas (e.g. SUM, AVERAGE etc.) into categories
– Also it includes the ability to check or audit formulas
• Data
– Includes: the ability to get data from other sources (e.g. MS-Access
database), sort and filter data, run different scenarios “what-if analysis”
on the data and determine results.
• Review
– Similar to the identically named feature in Word and includes: the ability
to annotate the spreadsheet, translate to other languages, spell check
and thesaurus look up.

MS-Excel tutorial notes by James Tam

Quick Summary Of Features In Each Tab Of The

Ribbon (3)
• View
– Allows the spreadsheet to been viewed in different ways (e.g. zoom in
and out, lock or ‘freeze’ parts so they stay visible even when scrolling
through a long spreadsheet).
• Draw (Office 2019 only)
– Some of the ability to insert simple shapes (e.g. lines, textbox, simple
geometric shapes) was moved from “Insert” to “Draw” with new
abilities added (e.g. adding a physical ruler that can assist in freehand
drawing).
• Relevant additional details will be provided in future tutorials
and in lecture.
• A good overview of the Excel tabs in the ribbon:
– [Link]
overview-of-microsoft-excel/
MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 6

 9/29/2020

Saving/Copying A Spreadsheet

• Similar to saving Word documents

– Save: save the spreadsheet under the current name (if not currently
named then a prompt to enter the name will appear).
– Save As: allows a copy of the spreadsheet to be saved under a different
name.
• Similar to Word documents Excel spreadsheets can also be tagged.

MS-Excel tutorial notes by James Tam

Creating PDF Version Of A Spreadsheet

• PDF (Portable document Format) documents

– Allows documents to be saved and viewed with different computer
operating systems.
– Ensures a consistent layout and formatting regardless of operating
system.
• Creating PDF documents using Excel via the “Save As” feature.
– Select ‘PDF’ as the file type under the pull down menu.

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 7

 9/29/2020

Excel Basics

MS-Excel tutorial notes by James Tam

Some Ways Of Navigating An Excel Spreadsheet

Keystroke Effect in Word

↑ (up arrow) Cursor moves up one row
↓ (down arrow) Cursor moves down one row
← (left arrow) Cursor moves left one column
→ (right arrow) Cursor moves right one column
Home key Cursor moves to the first column (Column A)
End Key Toggles End Mode on/off1
Page up Move a *window full* of rows up2
Page down Move a *window full* of rows down2

For a full list of keyboard shortcuts (include ones that allow navigating an
Excel sheet): [Link]
shortcuts-in-Excel-for-Windows-1798d9d5-842a-42b8-9c99-9b7213f0040f

1 In End mode, you can press an arrow key to move to the next nonblank cell in the same column or row as
the active cell.
2 The
MS-Excel exact notes
tutorial number of rows
by James will vary depending upon the size of the Excel window.
Tam

Spreadsheets and MS-Excel: The basics 8

 9/29/2020

Some Ways Of Navigating An Excel Spreadsheet (2)

Keystroke Effect in Word

<Alt>-<Page up> Move a *window full* of columns left3
<Alt>-<Page down> Move a *window full* of columns right3
<Control>-↑ Go to the first row4
<Control>-↓ Go to the last row5

For a full list of keyboard shortcuts (include ones that allow navigating an
Excel sheet): [Link]
shortcuts-in-Excel-for-Windows-1798d9d5-842a-42b8-9c99-9b7213f0040f

3 The exact number of columns will vary depending upon the size of the Excel window.
4 The first row will the first column with data, or if that row is the current one or the sheet is it empty then it will be
the first physical row 1.
5 The last row will the last row with data, or if that row is the current one or the sheet is it empty then it will be the
last physical row 1,048,576.

MS-Excel tutorial notes by James Tam

Some Ways Of Navigating An Excel Spreadsheet (3)

Keystroke Effect in Word

<Control>-← Go to the first column6
<Control>-→ Go to the last column7

For a full list of keyboard shortcuts (include ones that allow navigating an
Excel sheet): [Link]
shortcuts-in-Excel-for-Windows-1798d9d5-842a-42b8-9c99-9b7213f0040f

6 The first column will the first column with data, or if that column is the current one or the sheet is it empty then it
will be the first physical column A.
7 The column will the last column with data, or if that column is the current one or the sheet is it empty then it will
be the last physical column XFD.

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 9

 9/29/2020

Other Ways Of Navigating Excel

Clicking directly in
the editing area

Clicking in the
scrollbar

MS-Excel tutorial notes by James Tam

Entering Something Into A Cell

• Click on a cell

• Enter the data or formula into the cell

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 10

 9/29/2020

Copy/Cut-Pasting

• Right click and select the ‘Cut’ or ‘Copy’ option.

– Cut: the selection will be removed from the spreadsheet (when pasted)
– Copy: the selection will be duplicated (when pasted)
• These features can also be accessed via the Home tab as well.

Cut

Paste

Copy

MS-Excel tutorial notes by James Tam

Removing What’s In A Cell

• Options to do this can been accessed by right clicking when a

cell or cells have been selected in Excel

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 11

 9/29/2020

Removing What’s In A Cell (2)

• Clear contents (this feature can also be invoked by selecting a

cell or cells and pressing the ‘delete’ key)
– The contents of the cell are removed.
– Only the data is removed, other information such as background coloring
and font formatting (e.g. bolding) remains.
• Delete contents
– Does not just affect a single cell.
– This operation will delete a row/rows or column/columns from the
spreadsheet (resulting in other cells ‘moving’).

MS-Excel tutorial notes by James Tam

Deletions: Right Click

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 12

 9/29/2020

Inserting Rows Or Columns

• Similar to (but in reverse of) deleting rows or columns, this

operation will shift over other parts of the spreadsheet:

MS-Excel tutorial notes by James Tam

Inserting Rows And Columns

• Can be done via ‘right clicking’ at the insertion point

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 13

 9/29/2020

Insert Rows And Columns

• Can also be done via: Home->Insert

MS-Excel tutorial notes by James Tam

Contents Of A Cell: Types

• Raw data: also referred to as ‘constants’

• Label: describe the contents of another cell

• Formula: derived values, usually from the raw data (e.g.,

calculations: =2+2, lookup values: =D2*2, functions:
=sum(B2,B9))

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 14

 9/29/2020

Distinguishing Formulas From Data Or Labels

• In Excel all formulas must be preceded by the ‘=‘ symbol

(assignment) to distinguish it from a label
• Example spreadsheet: 1_formulas
– Label
2 + 2

– Formula
= 2 + 2

For the sake of brevity, you can assume that all formulas in this
section will be preceded by the assignment operator ‘=‘

MS-Excel tutorial notes by James Tam

Formatting Text

• It’s similar to Word, text formatting icons can be found under

the Home tab in the Font group.
– Word

– Excel

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 15

 9/29/2020

Formatting Numbers

• Properties of numerical data: number of fractional digits

displayed, should currency symbols be displayed, separators
between groups of numbers etc.

MS-Excel tutorial notes by James Tam

Changing Cell Alignment

• Again it’s similar to Word but the icons are located in a

different group.
– Word (Paragraph group)

– Excel (Alignment group)

• Also with Excel there’s icons for vertical alignment (the 3 icons on the first row)
as well as horizontal alignment (the 3 icons on the second row).
MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 16

 9/29/2020

Changing Cell Indenting

• Indenting can be increased or decreased via:

– Home -> Alignment group

– No indenting levels (all indenting levels are identical).

– Indenting applied to Row 2 & 3.

MS-Excel tutorial notes by James Tam

Other Ways Of Formatting Cells Via “Right Click”

• Excel provides the ability to format the

spreadsheet in various locations of the ribbon.
• You also can access these functions in the
context of a cell or cells in the spreadsheet.
1. Select a cell or cells for which you wish to apply
similar formatting effects.

2. Right click and select “Format Cells”

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 17

 9/29/2020

Formatting Cells: Text Alignment

MS-Excel tutorial notes by James Tam

Formatting Cells: Font (Text) Formatting

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 18

 9/29/2020

Formatting Cells: Borders Around Cells

MS-Excel tutorial notes by James Tam

Formatting Cells: Cell Fill Effects

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 19

 9/29/2020

Formatting Cells: Choices Of Cell Data

• General: No special format

• Number:
• Separator (1 comma for 3 digits)
• Several options for displaying
negative numbers
• Currency:
• Currency sign appears
• Several options for displaying
negative numbers
• Columns aligns decimal points
• Accounting:
• Similar to currency but no special
options for displaying negative
values
• Date, Time:
• Both allow the display to appear
in different formats
• Percentage: E.g. 100%
• Fraction: e.g. when 0.75 is typed in
it displays as 3/4
MS-Excel tutorial notes by James Tam

Formatting Cells: Choices Of Cell Data

• Scientific: E.g. 1.23E+06

• Decimal point shifting =
Exponent
• Text:
• Treats everything (even
numbers) as text
• Cell data is displayed exactly as
entered.
• Special:
• Country specific information
(e.g. zip)
• Custom:
• You can create your own formats
e.g. ID number = A22-B (alpha,
two digits, dash, alpha)

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 20

 9/29/2020

Formatting Cells: Choices Of Cell Data, Example

• Example spreadsheet: data_types

• (You can view the effect of setting different formatting types in
this spreadsheet).

MS-Excel tutorial notes by James Tam

Data Too Big For Your View

• Covered in the lectures notes

– Freeze panes
– Simple merging of cells (merging cells on a single row).

• Covered in the introductory tutorial notes (these notes)

– Resizing rows or columns
– Wrap the data
– Merge data (other ways of merging as well as merging multiple rows as
well as multiple columns)

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 21

 9/29/2020

Resize Width (Column)

• Resizing columns:
– Move the mouse to just after the end of the column to be resized (mouse
pointer icon changes appearance to indicate Excel is in “resize column
mode”).

– In the image Column A can be resized.

– While the pointer is in resize mode, move it left (reduce width) or right
(increase width).

MS-Excel tutorial notes by James Tam

Resize Height (Row)

• Resizing rows:
– Move the mouse to just after the bottom of the row to be resized (mouse
pointer icon changes appearance to indicate Excel is in “resize row
mode”).

– In the image Row 2 can be resized.

– While the pointer is in resize mode move it up (reduce width) or down
(increase width).
MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 22

 9/29/2020

Resizing Multiple Rows Or Columns

• To resize all columns or rows:

– Select all the columns or rows by pressing the <Ctrl> key and the ‘A’ key
simultaneously.
– Now when the mouse pointer is in column (or row) resize mode all the
columns (or rows) in the spreadsheet will be resized.
• To resize selected columns or rows:
– Press the <Ctrl> key and without releasing the key, click on the
columns (or rows) to be resized.
• This allows multiple columns (or rows) to be selected.
• Now when the mouse pointer is in a column (or row) resize mode all the
selected columns (or rows) in the spreadsheet will be resized.

MS-Excel tutorial notes by James Tam

Wrapping Data

• When the data in a cell is too wide for a column the data can
be ‘wrapped’ or made to continue on the next line.
• Example starting spreadsheet:
“data_too_wide” (Row 1 data is too wide for the column)

• Example starting spreadsheet (Row 1 text is wrapped):

wrapped_data

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 23

 9/29/2020

Merging Data

• Example starting spreadsheet:

merging_example_too_wide_data
Cell A1: CPSC 203

B,C,D: before
merge
Cell B1: Term grades, lecture 01

MS-Excel tutorial notes by James Tam

Options For Merging Cells In Excel: Merge & Centre

• “Merge & center” (e.g. 7B: Columns B-D “after” merge):

“merging_exammple_merge_and_centre”

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 24

 9/29/2020

Options For Merging Cells In Excel: Merge Cells

• Merge across: “merging_example_merge_cells”

– Description of how it works from MS-Excel tooltip: “Merge the selected
cells into one cell”)
– JT’s translation of MS-Excel help: only the data in the top left most cell in
the selected range is retained.
– e.g. Merging in the range A2:D6 only retains the data in Cell A2.

After
Before

MS-Excel tutorial notes by James Tam

Merge Cells: Unmerge Applied

• “Unmerge cells”: only unmerges the cells, data is not restored

After (unmerge)

• JT: instead of using ‘Unmerge’ use the ‘undo’ feature of MS-

Office: Simultaneously press the keys <Ctrl>-<y>

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 25

 9/29/2020

Options For Merging Cells In Excel: Merge Across

• “Merge across”: “merging_example_merge_across”

– Description of how it works from MS-Excel tooltip: “Merge selected cells
in the same row into one larger cell”
– JT’s translation of MS-Excel help: only the data in the left most column in
the selected range is retained.
– e.g. Merging in the range A2:D6 only retains the data in Col A.

Before After (merge across)

MS-Excel tutorial notes by James Tam

Merge Across: “Taking Back” Results

• Similar to the “Merge cells” you should use the ‘Undo’ feature
rather than the ‘Unmerge’ feature if you want to retain your
original data in the merged cells.

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 26

 9/29/2020

Hiding/Showing Columns

• Sometimes you don’t want to delete a column (data needed

e.g. student ID) but you want to temporarily obscure one or
more columns
• Hide:
– Select columns to hide, right click and select ‘hide’ (click at very top of
column)
– (Press control and click on the columns to hide 1+ non-adjacent columns)
• Show:
– Select the column adjacent to the hidden column and select ‘show’ (the
mouse pointer changes appearance)
– (Or simply move the mouse to the appropriate location – see above point
– and then ‘drag’ a hidden column into appearance)
• Specific resource for this feature
– [Link]
MS-Excel tutorial notes by James Tam
44ee-a614-dde8443579f8

Hiding/Showing Rows

• Hiding/showing rows works the same way but select a row or

rows by clicking to the far left not the top
• Individual cells cannot be hidden

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 27

 9/29/2020

How To Enter Formulas That Refer To Other Cells

• Approach 1: manually type in the formula
– Click on a cell where you want to enter the formula e.g. click on C2
– Type in the formula manually e.g. type =A2*B2

• Approach 2: type and click (used when a formula refers to a

cell)
– Click on a cell where you want to enter the formula e.g. click on cell C2
– When you get to the part of the formula that refers to another cell then
just click on the cell rather than typing in the information e.g. Entering
formula into cell C2, after typing the ‘=‘ click in in Cell A2
2) Reference to Cell
A2 appears here

1) Click
MS-Excel tutorial notes by James Tam here

Using Pre-Created Styles

• Similar to Word, Excel comes with pre-created text styles (font,

color, font effects such as bolding etc.)
– Home -> Styles : Cell Styles
– Also new styles can be created.

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 28

 9/29/2020

Using A Pre-Created Spreadsheet

• Similar to using pre-created styles but these sheets provide

more e.g. labeled headings and calculations for a family budget
in addition to pre-set formatting effects.
• Finding a style:
Enter the information
about the desired pre-
created spreadsheet

MS-Excel tutorial notes by James Tam

Headers And Footers

Header: appears at the top
of each page

Footer: appears at the

bottom of each page
MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 29

 9/29/2020

Inserting Headers And Footers

• Insert -> Text : Header & Footer

[Link]
28D2FC16AD31
MS-Excel tutorial notes by James Tam

Page Breaks

• Inserting or editing page breaks:

– Page Layout -> Page Setup : Breaks (Insert Page Break
/ Remove Page Break / Reset All Page Breaks)

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 30

 9/29/2020

Page Breaks

• Viewing the location of page breaks:

– View -> Workbook Views : Page Break Preview

– The heavy blue line signifies a break (in the example below Row 34 is on
a new page).

[Link]
MS-Excel tutorial notes by James Tam
e99ae2182a7e

Printing An Entire Spreadsheet

• File -> Print

Print preview provided when selecting the option to print

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 31

 9/29/2020

Print Orientation: Portrait

• This the default

• “Longer than wider”

MS-Excel tutorial notes by James Tam

Print Orientation: Landscape

• “Wider than longer”

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 32

 9/29/2020

Some Other Print Options

• Scaling: the printed size of the spreadsheet can be changed to

fit more (or less) of the sheet on each page.
• Margins: similar to Word, the size of the margins surrounding
the spreadsheet can be customized.

MS-Excel tutorial notes by James Tam

Setting A Print Area

• Filters printing so only portions of a spreadsheet are printed.

• Setting the print area:
– Page Layout -> Page Setup : Print Area -> Set Print
Area

• Viewing the print area via print preview

– File -> Print

[Link]
ba1b-e99ae2182a7e
MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 33

 9/29/2020

Converting Between Versions Of Excel

• Different version of Excel have different features

– (Features of 2007+ vs. earlier versions):
– [Link]
not-transferred-to-other-file-formats-8fdd91a3-792e-4aef-a5bb-46f603d0e585

– (Using the latest version of Excel with spreadsheets created with older
versions)
– [Link]
2fd9ffcb-6fce-485b-85af-fecfd651a5ac
• Checking for compatibility issues in a document
– File->Info->Check for issues->Check compatibility
• Converting older versions to the newest version of Excel
– File->Info->Convert (the ‘convert’ option only appears for older
versions of Excel spreadspreadsheets).
– File->Save As (Excel workbook *.xlsx) i.e. save under the new file
type
MS-Excel tutorial notesto do the
by James Tam conversion.

Terminology

• Spreadsheet (referred to as a “workbook” by Microsoft)

– A Microsoft Excel file

• Worksheet
– A part of a spreadsheet

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 34

 9/29/2020

Formula References To Other Worksheets

• Example spreadsheet: “references_V1_10%tax”

“Employees” worksheet “Rates” worksheet

References to same worksheet Reference to another

worksheet

MS-Excel tutorial notes by James Tam

Copying/Moving A Worksheet

• Sometimes you may wish to duplicate the data and

calculations from one worksheet into another e.g. term grade
calculation from one lecture to another.
– Right click on the source worksheet and select ‘Move’ or ‘Copy’

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 35

 9/29/2020

Renaming Worksheets

• Right click on the appropriate worksheet and select the

‘Rename’ option.

MS-Excel tutorial notes by James Tam

Miscellaneous Features: Freezing Panes

• Freeze panes

• Options & method of invoking the option:

– Freeze top (currently visible) row: View -> Window : Freeze Panes
: Freeze Top Row
– Freeze left (currently visible) column: View -> Window : Freeze
Panes : Freeze First Column
– Freeze any row:
• Click on the row below the row to be frozen: View -> Window : Freeze
Panes : Freeze Panes
MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 36

 9/29/2020

Hiding/Showing Columns

• Sometimes you don’t want to delete a column (data needed

e.g. student ID) but you want to temporarily obscure one or
more columns
• Hide:
– Select columns to hide, right click and select ‘hide’ (click at very top of
column)
– (Press control and click on the columns to hide 1+ non-adjacent columns)
• Show:
– Select the column adjacent (right hand side) to the hidden column and
select ‘show’ (the mouse pointer changes appearance)
– (Or simply move the mouse to the appropriate location – see above point
– and then ‘drag’ a hidden column into appearance)
• Specific resource for this feature
– [Link]
MS-Excel tutorial notes by James Tam
44ee-a614-dde8443579f8

Hiding/Showing Rows

• Hiding/showing rows works the same way but select a row or

rows by clicking on an adjacent row (below the hidden row or
rows)
• Individual cells cannot be hidden

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 37

 9/29/2020

Charting Information In Excel

• In tutorial you learned how to insert a column chart (‘grows’

vertically).
• Some other types of charts covered in this section include: bar
charts and pie charts.
• Also you will learn how to filter chart data.

MS-Excel tutorial notes by James Tam

Other Charts: Bar

• Example spreadsheet: example_creating_charts

• In Excel: a column chart grows vertically while a bar chart
grows horizontally.
– Column chart
Num grades
30
20
10
0
A A- B+ B B- C+ C C- D+ D F

– Bar chart
Num grades
F
D+
C
B-
B+
A
0 10 20 30

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 38

 9/29/2020

Other Charts: Pie

• Example spreadsheet:
example_creating_charts
• Show proportions rather than
exact values
– Example good usage: % Awarded each grade
proportion of students who
were awarded ‘A’ vs ‘A-’ grades
(i.e. roughly ¼ awarded a ‘B’
grade in the chart to the left)

A A- B+ B B- C+ C C- D+ D F

Num grades
– Example poor usage: exact
numbers of students who were
awarded A grades, A- grades
etc. (e.g. did 25, 26 or 30
students get a ‘B’?)
MS-Excel tutorial notes by James Tam
A A- B+ B B- C+ C C- D+ D F

Other Charts: Clustered Column

• Example spreadsheet: clustered_column_chart_filtering_data

• Used when multiple categories of data must be plotted
– E.g. 1: Number of births at different hospitals over the last year.
• One axis is the months of the year, the other is the number of births. With a 2D chart all
dimensions are accounted for.
– E.g. 1: Rating scores for different questions comparing an individual vs.
department vs. faculty scores.
• One axis is for the different questions, one axis is for the actual score.
• Plotted as a column chart additional columns can be used to display scores.

• This type of chart allows comparison between the categories (e.g.

individual
MS-Excel tutorial department for each of the 12 questions).
vs. Tam
notes by James

Spreadsheets and MS-Excel: The basics 39

 9/29/2020

Filtering Chart Data

• (The previous example spreadsheet may be used here).

• Under different situations it may be desired to see only a
subset of the data.
– In the previous example suppose that for one type of evaluation the
individual was to be judged only vs. other members of the department
and not the whole faculty.

– The filtering temporarily affects what data is charted and can be toggled
(turned on/off).
MS-Excel tutorial notes by James Tam

Filtering Chart Data (2)

• Step 1: Right click on the chart and click on the “Select Data”
option

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 40

 9/29/2020

Filtering Chart Data (3)

• Step 2: Check or Uncheck the data that you wish to be charted

(in the example below we have unchecked the option to chart
faculty scores).

MS-Excel tutorial notes by James Tam

Resources For Useful Excel Charting Features

• Moving and resizing (within and between worksheets):

– [Link]
F9D6087B-1D7B-4A72-8D81-1D0788EA783F
• Editing titles and data labels:
– [Link]
chart-248bfe15-02ed-477a-81b6-b14e2c03b87e
• Adding titles to the axis
– [Link]
5861B8D8-7FC8-441E-87D5-4CD9CD2B46CE
• Changing data labels
– [Link]
labels-in-a-chart-EE7525E3-3A58-4142-B0E3-8140A1D6545E

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 41

 9/29/2020

Quitting Excel

• Similar to Word
• Click on the close window icon

• Select the close option: File->Close

MS-Excel tutorial notes by James Tam

Other Excel Resources

• Online training resources created by Microsoft:

– Tutorials
• [Link]
e94c-46af-a5b3-d7c22f6990bb
– A MAC specific resource
• [Link]
aec0-4da7-b381-9cc1b9b47745

MS-Excel tutorial notes by James Tam

Spreadsheets and MS-Excel: The basics 42