excel guide handbook112
TRANSCRIPT
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 1/43
Basic Excel Handbook • Page 23
From the Formatting
toolbar, click the Fontcolor button and chooseyour desired
color.
Color Fonts
You may want to change more than just the font style and font size: you may want to change thefont color.
Follow the step below to change the Font Color .
• Complete Step A as shown below.
A
Highlight the data youwish to color, and thenchoose your desiredcolor.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 2/43
Basic Excel Handbook • Page 24
Shade or Put Patterns In Cell(s)
To focus attention on particular areas of the worksheet, such as the column or row labels orimportant totals, fill the cell background with color and/or a pattern.
Follow the steps below to provide Shadi ng or Pat t erns in a Cell .
Complete Steps A-F. Steps A–D are shown below. Steps E–F are shown on the following pages.
AClick and movethe mouse overthe cells you wishto color.
From the Formatting toolbar,
choose Cells.
C In the Format Cells dialog box,click the Patterns tab, and
then choose your desired color.
In the Patterns tab, click thePattern drop-down arrow toaccess patterns.
You can choose from a widervariety of color fills in theFormat Cells dialog box.
D
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 3/43
Basic Excel Handbook • Page 25
Choose your desired Pattern.
Click OK .
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 4/43
Basic Excel Handbook • Page 26
Note the filled cells, and the Fill Color button has thecorresponding selected color.
From the Formatting toolbar you can alsoadd color to a cell using the Fill Colorbutton.
Cells with shading and a pattern.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 5/43
Basic Excel Handbook • Page 27
Print Gridlines
Gridlines mark the cell borders. The Sheet tab of the Page Setup dialog box provides an option forprinting gridlines with your data. You can also print your worksheet in black and white (even if itincludes color fills or graphics).
Follow the steps below to print Gridl ines.
• Complete Steps A-D. Step A is shown below. Steps B–D are as shown on the following pages.
A
From the File menu, choosePage Setup.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 6/43
Basic Excel Handbook • Page 28
From the Page Setup dialog
box, click the Sheet tab. B
In the Print options click
Gridlines. C
The next time you print the gridlineswill appear.
Click OK . D
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 7/43
Basic Excel Handbook • Page 29
Create Borders
By default, Excel applies a ½-pt. black solid line border around all table cells. Use the Borders toolbar button to change the borders of table cells. You can select borders before you draw newcells or apply them to selected cells.
Follow the steps below to Apply a Border .
Complete Steps A-F. Steps A–B are shown below. Steps C–F are shown on the following pages.
AFrom the Formatting toolbar, click the Borders button drop-down arrow to access theDraw Borders toolbar.
Click the Draw Borders toolbar.
The Draw Borders toolbar displays afterStep B.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 8/43
Basic Excel Handbook • Page 30
CClick the Font drop-down arrow todisplay the different styles andthicknesses of lines.
Choose the line style you desire.
From the Borders’ toolbar, clickthe Erase button, then click the
line(s) you wish to delete.
Click on the Erase button and the LineColor button to turn on and off (like youwould a light switch).
From the Borders’ toolbar, clickthe Line Color button, then choose
the colors(s) you desire.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 9/43
Basic Excel Handbook • Page 31
Delete a Border
The Draw Borders toolbar also contains the erase borders button. There are times you will want tochange the border styles or completely delete a border.
Follow the steps below to Delet e a Bord er .
• Complete Steps A–C as shown below.
Highlight the table of cells thathave a border.
A
In the Formatting
toolbar, click theBorders’ drop-down
arrow.
Choose the of the Erase option.C
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 10/43
Basic Excel Handbook • Page 32
Merge & Center Cells
The Merge and Center button is used to center information across a select range of cells. Typically,the Merge and Center button is used to center the title on a worksheet.
Follow the steps below to Merge and Cent er Cell s.
Complete Steps A-B as shown below.
ADrag across the cell withentry and adjacent cells
to select them.
From the Formatting toolbar, clickthe Merge & Center button.
Data is centered within the selected range. You can also left-or
right-align data within the merged cell by clicking the Align Left orAlign Right buttons on the Formatting toolbar.
To unmerge the cells (and createseparate cells again), click the Merge & Center button on the Formatting toolbarto turn it off.
B
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 11/43
Basic Excel Handbook • Page 33
Wrap Text
If you want text to appear on multiple lines in a cell, you can format the cell so that text wrapsautomatically or you can enter a manual line break.
Follow the steps below to Tex t Wrap .
Complete Steps A-E. Steps A–B are shown below. Steps C–E are shown on the following pages.
ASelect text to appear on
multiple lines in a cell.
BFrom the Format menu, choose Cells.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 12/43
Basic Excel Handbook • Page 34
Note the result ofWrap text.
CIn the Format Cells dialog box,
click the Alignment tab.
Under the Text control, click
Wrap text.D
EClick OK .
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 13/43
Basic Excel Handbook • Page 35
Vertical Text
Many times the label at the top of a column is much wider than the data stored in it. You can use theWrap text option (Format menu > Cells command > Alignment tab) to make a multiple-word labelnarrower, but sometimes that's not enough. Vertical text is an option, but it can be difficult to readand takes a lot of vertical space. You may want to try using rotated text and cell borders instead, asshown in the following picture.
Follow the steps below to create Ver t i ca l Text .
Complete Steps A–E. Steps A–B are shown below. Steps C–E are shown on the following pages.
From the Format menu, chooseCells.
B
AHighlight text.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 14/43
Basic Excel Handbook • Page 36
In the Format Cells dialogbox, click the Alignmenttab.
C
Under Orientation, choose the degree of
orientation.
D
Click OK.E
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 15/43
Basic Excel Handbook • Page 37
Resize Columns
There are two ways to resize a column. To resize or change the width of a column, you can use theMouse or the Menu. On a worksheet, you can specify a column width of 0 (zero) to 255. This valuerepresents the number of characters that can be displayed in a cell that is formatted with thestandard font.
The standard font is the default text font for worksheets. The standard font determines the defaultfont for the Normal cell style. If the column width is set to 0, the column is hidden.
Follow the step below to Resize Columns Using t he Mouse.
• Complete Step A as shown below.
Note the cell A1 cannot accommodatethe large of alpha data, and there is aneed to resize the cell.
The display in Cells A2 and A3 indicate there is morenumeric data than the cell can accommodate and the
cells should be resized.
APosition the cursor on the linethat separates Column A fromColumn B, and then doubleclick.
You can also click and drag with themouse to customize the size of thecolumn.
Note the display after thecolumn width has beenresized.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 16/43
Basic Excel Handbook • Page 38
Part IV:Saving Money and
Working Smart
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 17/43
Basic Excel Handbook • Page 39
Cumulative Fall and Spring Grade Point
Averages – Using the Average Function
A formula is a worksheet instruction that performs a calculation. The Average Function is used to findthe Fall and Spring grade point averages. The Average Function adds the grades in the Fall or Springgrading period and divides by the number of grading periods.
Follow the steps below to find the Cumulat ive Fal l and Spr ing Gra de Point Avera ges .
Complete Steps A–I. Steps A–D are shown below. Steps E–J are shown on the following pages.
Click in the cell where theAverage formula will display. In
this example Cell G1. A
Click the Function (fx) button.
B
D
C Select the Average functionfrom the Insert Function dialogbox.
Click OK .
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 18/43
Basic Excel Handbook • Page 40
Click on the blueFunction Argumentstitle bar and drag theFunction Argumentsdialog box down so thatyou can access the datathat needs to beaveraged.
EClick and drag tohighlight the cells thatneed to be averaged. Inthis example click onCells D1 – F1.
F
Note the Average formula displays inboth Cell G1 and the FunctionsArguments Average Number1.
Click OK or press Enter. G
The colon (:) represents through.For example D1:F1 means Cells D1through F1 are highlighted.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 19/43
Basic Excel Handbook • Page 41
Important: It is important that the formula is always placed in the FIRST ROW in order to copy theformula to all the cells in the desired column. Do not be alarmed that Cell G1 appears to have an errormessage, #DIV/0!, displayed. This message occurs because the Header Rows that contain both alphaand numeric information have been averaged.
Highlight Column G byclicking on G.
H
Click EDIT > FILL > DOWN tocopy the Average formula toall the cells in Column G.
I
Do not be alarmed that Cell G1appears to have an error message(#DIV/0!) displayed. This messageoccurs because the Header Rows thatcontain both alpha and numericinformation have been averaged.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 20/43
Basic Excel Handbook • Page 42
Note that all of the formulas havebeen successfully copied to all of thecells in Column G.
Delete the #DIV/0! message in Cell G1and type in the appropriate Header Rowtitle. For example “Fall CumulativeGPAs.”
J
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 21/43
Basic Excel Handbook • Page 43
Sort Alpha Data
Rows can be sorted according to the data in any column. For example, in a table of names andaddresses, rows can be sorted alphabetically by name or by city. Excel rearranges the rows in thetable but does not rearrange the columns. You can sort text in Ascending order (A-Z) or Descendingorder (Z-A).
Follow the steps below to Sort Alpha Dat a.
• Complete Steps A–D. A–C are shown below. Step D is shown on the following page.
AFrom the Data menu,
choose Sort.
BClick Continue withthe current
selection.
Click Sort. C
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 22/43
Basic Excel Handbook • Page 44
DClick OK.
The column will sort according tothe first name that appears in the
cell.
Column A is the column you wish
to sort by.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 23/43
Basic Excel Handbook • Page 45
Sort Numeric Data
You can sort numeric data in Ascending order (1-100…) or Descending order (…100-1).
Follow the steps below to Sort Numeric Dat a.
• Complete Steps A-D. Steps A–C are shown below. Step D is shown on the following page.
AFrom the Data menu
item, choose Sort.
BClick Continue with the
current selection.
Click Sort. C
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 24/43
Basic Excel Handbook • Page 46
DClick OK.
The Numeric Sort iscompleted, and Column C
displays the numeric data inAscending order.
Column C, the column you wish to
sort by, is displayed here.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 25/43
Basic Excel Handbook • Page 47
Insert Date at the Top of Worksheet
When you want to repeat the same information at the top of each page, create a header. You canselect a pre-designed header from those listed, or create customized ones. A customized header isseparated into three sections: Left (text is left aligned), Center (text is center aligned), and Right(text is right aligned).
Flip open a novel and look at the facing pages. Most likely, at the top of one page you'll see theauthor's name and at the top of the other page you'll see the book title. At the bottom will beconsecutive page numbers. These details are in the document's headers and footers.
Headers and footers in Excel have many benefits, one of the major ones being automaticrenumbering of pages if you add or delete content in your document.
Follow the steps below to create a Header.
• Complete Steps A–F. Step A is shown below. Steps B–F are shown on the following pages.
AFrom the File menu, choose
Page Setup.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 26/43
Basic Excel Handbook • Page 48
BFrom the Page Setup dialogbox, click the Header/Footer
tab.
In the Header/Footer tab, click
Custom Header.
C
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 27/43
Basic Excel Handbook • Page 49
In the Custom Header dialogbox, choose the Left section
and click the Date button.D
You also have the option toposition the date at the Center
section or Right section.
In the Header/Footer tab, the
Header displays the date.
Click Print Preview.
E
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 28/43
Basic Excel Handbook • Page 50
Note all the options in PrintPreview: Zoom, Print, Setup,Margins, Page Break Preview,Close and Hel .
Print Preview displays the
header on the worksheet.
FClick Print.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 29/43
Basic Excel Handbook • Page 51
Insert Page Number at the Bottom Page
When you want to repeat the same information at the bottom of each page, create a footer. Youcan select a pre-designed header from those listed or create customized ones. A customized headeris separated into three sections: Left (text is left aligned), Center (text is center aligned), and Right(text is right aligned).
Follow the steps below to create a Footer.
• Complete Steps A–H. Step A is shown below. Steps B–H are shown on the following pages.
A From the File
menu, choose PageSetup.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 30/43
Basic Excel Handbook • Page 52
In the Page Setup dialog box,click the Header/Footer tab.
B
Click the Custom
Footer button.C
Click OK. D
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 31/43
Basic Excel Handbook • Page 53
In the Footer dialog box,click in the Left section and choose the Page
button.
E
FClick OK.
Click Print Preview. G
In the Header/Footer tab of the Page Setup
dialog box, the Footerdisplays the Footer pagenumber (1).
You can choose other buttons(date, time, file path, filename, ortab name), or to locate the data inthe Center section or Right
section.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 32/43
Basic Excel Handbook • Page 54
Print Preview displays the Footer page
number at the bottom of this page.
Click Print. H
Note all the options in PrintPreview: Zoom, Print, Setup,Margins, Page Break Preview,
Close and Help.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 33/43
Basic Excel Handbook • Page 55
Print the Top Row on Each Page
It is important to have the labels for the worksheet to carry over to other worksheets so that thedata makes sense.
Follow the steps below to Pr int To t he Top Row on Each Page.
• Complete Steps A–F. Step A is shown below. Steps B–F are shown on the following pages.
From the File menu,
choose Page Setup.A
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 34/43
Basic Excel Handbook • Page 56
BIn the Page Setup dialog box,
click the Sheet tab.
CIn Print titles, click Rows to
repeat at top.
Click the row you choose toprint on the top of each pageand press the Enter key.
D
Note the Page Setup – Rows to repeat at top toolbardisplays after clicking the row to appear at the top of
each page.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 35/43
Basic Excel Handbook • Page 57
Click OK.E
From the File menu, clickPrint Preview.
F
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 36/43
Basic Excel Handbook • Page 58
Page 1
Page 2
The Print Preview displays the ColumnHeadings on all pages after completing StepsA–F.
The Print Preview displays the ColumnHeadings on all pages after completing StepsA–F.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 37/43
Basic Excel Handbook • Page 60
BFrom the PageSetup dialog
box, click Page tab.
In the Page tab, click the
Landscape Orientation.C
In the Page tab, click Print
Preview.D
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 38/43
Basic Excel Handbook • Page 61
In the Print Preview, you have the following options: see the next pageof the worksheet (Next), enlarge the view of the worksheet (Zoom),Print, access Page Setup (Setup), change margins (Margins), adjustwhere the page breaks are by clicking and dragging with your mouse
(Page Break Preview), Close, or Help.
PortraitOrientation
(vertical) printout.
LandscapeOrientation (horizontal) rintout.
Click Print.
E
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 39/43
Basic Excel Handbook • Page 62
Print the Worksheet on One Page
Overview: To scale data, reduce or enlarge information, use the Adjust to % normal size option onthe Page Setup dialog box from the Page Setup or Print Preview commands on the File menu. Usethe Fit to pages option to compress worksheet data to fill a specific number of pages.
Follow the steps below to Reduce Dat a T o One Page .
• Complete Steps A–E. Step A is shown below. Steps B–E are on the following pages.
AFrom the File menu, choosePage Setup.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 40/43
Basic Excel Handbook • Page 63
B In the Page Setup dialog box,click the Page tab.
In the Scaling option, Adjust to50%, rather than the default100% normal size setting.
50
Click Print Preview.
C
D
You may also want to change the pageOrientation from Portrait (vertical) to Landsca e horizontal .
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 41/43
Basic Excel Handbook • Page 64
Before scaling the data, only Columns A-G
would fit on a page.
After reducing the data, there are morecolumns included on the worksheet
printout (Columns A-N)
Click Print. E
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 42/43
Basic Excel • Page 65
Preview Worksheet Without Printing
Why use Print Preview before printing my worksheet? Print Preview permits you to view the outputbefore you print, and the use of this feature will save ink and paper.
Follow the step below to Previ ew You Wor ksheet(s).
• Complete Step A as shown below.
AIn the Formatting toolbar,click the Print Preview button.
7/26/2019 Excel Guide Handbook112
http://slidepdf.com/reader/full/excel-guide-handbook112 43/43
In the Print Preview, you have the following options: see the nextpage of the worksheet (Next), enlarge the view of the worksheet(Zoom), Print, access Page Setup (Setup), change margins (Margins),adjust where the page breaks are by clicking and dragging with your
mouse (Page Break Preview), Close, or Help.