MS Excel

MINERAL RESOURCES INSTITUTE

COMPUTER APPLICATIONS
CTT04103/04105
2020/2021
 Objectives
 Define the terms spreadsheet and
worksheet.
 Identify the parts of a worksheet.
 Start Excel, open an existing workbook,
and save a workbook.
 Move the active cell in a worksheet.

2
2
 Objectives (continued)
 Select cells and enter data in a worksheet.
 Edit and replace data in cells.
 Zoom, preview, and print a worksheet.
 Close a workbook and exit Excel.

3
3
 Vocabulary

 active cell  Formula Bar

 active worksheet  landscape
 adjacent range orientation
 cell  Microsoft Excel 2010
 cell reference (Excel)
 column
 Name Box
 formula
 nonadjacent range
 portrait orientation

4
4
 Spreadsheets

 Microsoft Excel 2010 is the

spreadsheet program in Microsoft Office
2010.
 A spreadsheet is a grid of rows and
columns in which you enter text,
numbers, and the results of
calculations.
 In Excel, a computerized spreadsheet is
called a worksheet. The file used to
store worksheets is called a workbook.
5
5
 Starting Excel

 You start Excel from the Start menu in

Windows. Click the Start button, click
All Programs, click Microsoft Office, and
then click Microsoft Excel 2010.
 The Excel program window has the
same basic parts as all Office programs:
the title bar, the Quick Access Toolbar,
the Ribbon, Backstage view, and the
status bar.
6
6
Starting Excel (continued)
 Excel program window

7
 Exploring the Parts of the
Workbook

 Each workbook contains three worksheets

by default. The worksheet displayed in the
work area is the active worksheet.
 Columns appear vertically and are
identified by letters. Rows appear
horizontally and are identified by numbers.
 A cell is the intersection of a row and a
column. Each cell is identified by a unique
cell reference.
8
8
 Exploring the Parts of the
Workbook (continued)

 The cell in the worksheet in which you can

type data is called the active cell.
 The Name Box, or cell reference area,
displays the cell reference of the active cell.
 The Formula Bar displays a formula when a
worksheet cell contains a calculated value.
 A formula is an equation that calculates a
new value from values currently in a
worksheet.
9
9
 Zooming a Worksheet

 You can change the magnification of a

worksheet using the Zoom controls on
the status bar.
 The default magnification for a
workbook is 100%.
 For a closer view of a worksheet, click
the Zoom In button or drag the Zoom
slider to the right to increase the zoom
percentage. 10
1
Zooming a Worksheet
(continued)
 Zoom dialog box and controls

11
 Previewing and Printing a
Worksheet

 You can print a worksheet by clicking

the File tab on the Ribbon, and then
clicking Print in the navigation bar to
display the Print tab.
 The Print tab enables you to choose
print settings.
 The Print tab also allows you to preview
your pages before printing.
12
1
 Closing a Workbook and
Exiting Excel

 You can close a workbook by clicking

the File tab on the Ribbon, and then
clicking Close in the navigation bar.
Excel remains open.
 To exit the workbook, click the Exit
command in the navigation bar.

13
1
Save a Workbook
 Click on Office Button and select
Save or Save As.
 Save: save the workbook
as .xlsx. This format is Excel
2007 compatible. It cannot be
opened in previous version of
Excel unless you have an Office
2007 converter installed.
 Save As:
 Excel Workbook - .xlsx
 Excel 97 – 2003 Workbook - .xls
 Other Formats - .CSV and others.
Entering Data in a Cell
 Worksheet cells can contain text, numbers,
or formulas.
 Text is any combination of letters and numbers
and symbols.
 Numbers are values, dates, or times.
 Formulas are equations that calculate a value.
 You enter data in the active cell.

15
 Changing Data in a Cell

 You can edit, replace, or clear data.

 You can edit cell data in the Formula Bar or
in the cell. The contents of the active cell
always appear in the Formula Bar.
 To replace cell data, select the cell, type new
data, and press the Enter button on the
Formula Bar or the Enter key or the Tab key.
 To clear the active cell, you can use the
Ribbon, the keyboard, or the mouse.
16
1
 Searching for Data

 The Find command locates data in a

worksheet, which is particularly helpful
when a worksheet contains a large
amount of data. You can use the Find
command to locate words or parts of
words.
 The Replace command is an extension
of the Find command. Replacing data
substitutes new data for the data that 17
1 the Find command locates.
Creation of a Workbook
 You should always enter headings to columns and
rows to identify what the numbers represent.
 Practice: make a workbook of home expenses.

Items January February March Total Average

Clothing

Grocery

Gas

Utilities

Total
Data Entry
 Place your mouse in a cell and click once.
This will allow you to enter data in that
cell.
 To move HORIZONTALLY across cells, hit
TAB.
 To move VERTICALLY, hit ENTER.
 Practice:
 Enter column heading and row heading.
 Enter data.
Autofill – Fill in Months
AutoFill Enter the months of the year, the days of the
week, multiples of 2 or 3, or other data in a series. You type
one or more entries, and then extend the series.
 Fill in the months of the year
 Type in the first 2 months.
 Change the cell type to Date type.
 Select the row of the months by clicking on the row tab such as “1”. Go to Format and
select Format Cells… (bottom).
 Select Date and click on OK.

•Highlight the cells of the two

months and drag the bottom
right corner to expand the cells
with the rest of the months.
Types of Data
 You can enter numerical or text data in a cell.
 Enter numbers in cells. You may need to change the cell
format to numbers.
 Highlight number cells in the practice file, go to Format and select Cell
Format. Select Number and click on OK.
 If you see ######, you need to expand your column so
the data fits.
 Double click on the line between the two column headings to auto-fit.
 Drag the border between two columns.
 Change numbers to Currency with $ sign.
 Highlight all number cells and click on $ icon.
 To enter fractions, leave a space between the whole
number and the fraction. For example, 1 1/8.
 To enter a fraction only, enter a zero first. For example, 0
1/4. If you enter 1/4 without the zero, Excel will interpret
the number as a date, January 4.
Insert a Row/Column
 Insert a row:
 Select the row you would like to insert above
 Clicking on the row number tab.
 In Home tab, go to Insert and select Insert Sheet Rows.

 Insert a column:
 Select the column you would like to insert next to it
 Clicking on the column letter tab such as L.
 In Home tab, go to Insert and select Insert Sheet
Column.
Change Column Width or Row
Height
 Column Width
 Drag the border between two columns to
adjust a column width.
 Adjust column width for a group of columns
 Highlight the columns you want to adjust their width.
 In Home tab, go to Format and select Column
Width...
 Enter a number of characters for column width. Click on
OK.
 Row Height
 Drag the border between two rows to adjust a row width.
 Adjust row width for a group of rows
 Highlight the rows you would like to change their height.
 In Home tab, go to Format and select Row Height.
 Enter a number of the row height and click on OK.
 One point=.035 cm
 Format a Worksheet

 Change the font size, color, and the

background of a cell or group of cells.
 Select the cells you’d like to change. Then
select a formatting tool.
 To show cell borders, highlight the cells
and select a border.
Table Styles and Cell Styles
 Table Styles
 Highlight the Excel table (all cells), go to
Format as Table icon. Select a table style.
 Cell Styles
 Highlight cells, go to Cell Styles, select a
cell style.
Performing mathematical tasks
Formulas & functions
Excel reads any
expression that
begins with an
equal sign as a
calculation. All
functions and
formulas begin
with an equal
sign.
 The function =SUM(B1:B6)
 The formula =B1+B2+B3+B4+B5+B6
 In Excel, a "formula" is a user-created
equation that performs calculations on
data within a spreadsheet, while a
"function" is a pre-defined calculation
provided by Excel to perform specific tasks
like summing, averaging, or finding
maximum values
Formulas & Functions
Basic Calculating Functions –
Total, Average
 Excel has mathematical functions for
you to use.
 Total
 Click on the Cell that displays a total.
 In Home tab, click on the sum function icon.
 Highlight the cells included in the total and
hit Enter key.
 Average
 Click on the cell that displays an average.

 In Home tab, click on the little down arrow in

the sum function icon and select Average.

 Highlight the cells included in the average

and hit Enter key.

Creating Basic Formula
 You conduct a mathematical
calculation in Excel by typing a
simple formula into a cell. An Excel
formula always begins with an
equal sign (=).
 Math operators
 Addition: +
 Subtraction:-
 Multiplication:*
 Division:/
 Example: Gas + Utilities
 Click on the cell that displays the
expense of Gas and Utilities.
 Enter =.
 Click on the Gas cell for January.
 Enter +.
 Click on the Utilities cell for January
 Hit Enter key.
Copy a Formula
 You may copy the same formula onto a
series of cells.
 Example, a total expense in each of all 12 months.
 Select the total cell for January.
 Drag the bottom right corner of the cell to expand to the
December total cell.
 The total expense is then calculated for all 12 months.

• Practice
• Total for each of
the categories
Merge and Center
 You may want to add a title for an Excel table.
 Insert a row above the column heading row.
 Type the title in the first cell of the title row.
 Highlight the cells you would like to display the
table title.
 Click on Merge and Center icon.
Print an Excel Sheet
 As default, there are no borders
around cells.
 For printing, there are two ways to
print boarders around cells.
 Gridlines: This way adds gridlines around
the cells in the table.
 Click on Page Layout tab.
 Click on Page Setup group.
 Click on Sheet tab.
 Check Gridlines. Click on OK.
 Add borders: This way adds borders around
the cells you selected.
 Highlight the cells you want to have borders.
 In Home tab, click on the down arrow next to
the border icon and select a choice of
borders.
 You have flexibility of selecting a variety of
borders.
Page
Layout
 Orientation
 The vertical dotted line specifies the right border
of a page in a spreadsheet.
 You may change the page orientation from
Portrait to Landscape. Go to Page Layout tab,
click on the Orientation icon and select
Landscape.
 You may adjust the width of columns to fit the
columns into a page.
 Double click on the border between the titles of two
columns to automatically adjust the column width.
 Drag the border between the titles of two columns to
adjust the column width.
 Margins
 To adjust the margins of a page, in Page Layout
tab, click on Margins icon and select Custom
Margins. Change margins and click on OK.
 Sheet Name
 To give a name of a sheet, double-click on the
sheet tab and enter the name.
Column and Pie
Chart

 A column chart to show

monthly expenses.

 A column chart to show

the comparison of
expenses in selected
months.

 A pie chart to see the

percentage/amount of
each expense category.
Column Chart -Monthly Expense
 Highlight the expenses with the headings of 12 months.
 Click on Insert tab.
 Click on the arrow in Columns icon in Charts group.
 Select a column chart.
 To enter a title for the chart, choose a chart layout in Chart Layouts group.
click on the Title Box and type a title.
 You may change a layout or a style of the chart by selecting a style or a
layout.
Column Chart - Expense
Comparison
 Display expense comparisons among January, March, and June.
 Four columns: Items, January, March, and June. Use “ctrl” key to select
multiple columns.
 Go to Insert tab and select a column chart under Column chart icon.
 Click on Switch Row/Column icon to change the comparison:
comparison among months’ categories.
Pie Chart - Expense
Distribution
 Highlight the Items column
and the Total Expense Column
(ctrl).
 Go to Insert tab.
 Select a Pie chart in Pie icon.
 To show a percentage, click
on the first icon in Chart
Layouts group.
Pie Chart - Format
 To add a title for the pie chart, choose a layout with a title
box, click on the title box, and type the title.
 Add background for the chart: Right-click on the chart and
select Format Chart Area….
 Select a Fill style, border color, border style, etc. Click on Close.
Key Steps in Charting
 Create the columns/rows that have the data you need to
draw a chart.
 Select the columns/rows needed.
 Hold “ctrl” key to select non-continuous columns.
 Hold “shift” key to select continuous columns.
 Select a chart type in Insert tab.
 Enter Chart title.
 Select a style of a chart.
 Summary

In this lesson, you learned:

 The primary purpose of a spreadsheet is to
solve problems involving numbers. The
advantage of using a computer spreadsheet is
that you can complete complex and repetitious
calculations quickly and accurately.
 A worksheet consists of columns and rows that
intersect to form cells. Each cell is identified by
a cell reference, which combines the letter of
the column and the number of the row.
41
4
 Summary (continued)

 The first time you save a workbook, the Save As

dialog box opens so you can enter a descriptive
name and select a save location. After that, you
can use the Save command in Backstage view or
the Save button on the Quick Access Toolbar to
save the latest version of the workbook.
 You can change the active cell in the worksheet
by clicking the cell with the pointer, pressing
keys, or using the scroll bars. The Go To dialog
box lets you quickly move the active cell
anywhere in the worksheet. 42
4
 Summary (continued)

 Worksheet cells can contain text, numbers,

and formulas. After you enter data or a
formula in a cell, you can change the cell
contents by editing, replacing, or deleting it.
 You can search for specific characters in a
worksheet. You can also replace data you have
searched for with specific characters.

43
4
 Summary (continued)

 The zoom controls on the status bar enable

you to enlarge or reduce the magnification of
the worksheet in the worksheet window.
 Before you print a worksheet, you should
check the page preview to see how the printed
pages will look.
 When you finish your work session, you should
save your final changes and close the
workbook.

44
4