CHAPTER 1:   PREPARING AND FORMATTING AN EXCEL WORKSHEET

·         AN EXCEL SPREADSHEET CAN BE USED FOR SUCH ACTIVITIES AS CREATING FINANCIAL STATEMENTS, PREPARING BUDGETS, MANAGING INVENTORY, AND ANALYZING CASH FLOW.  YOU CAN ALSO DO “WHAT IF” ANALYSES.

·         CREATING A WORKSHEET – A DOCUMENT CREATED IN EXCEL IS REFERRED TO AS A WORKBOOK.  A WORKBOOK CONSISTS OF INDIVIDUAL WORKSHEETS.  THE AREA WITH THE GRIDLINES IN THE EXCEL WINDOW IS CALLED THE WORKSHEET AREA.  IN THIS AREA, YOU CREATE A WORKSHEET THAT WILL BE SAVED AS PART OF A WORKBOOK.  A WORKBOOK CAN CONTAIN 1 TO 255 WORKSHEETS.  (NOTICE THE TABS AT THE LOWER LEFT OF THE SCREEN INDICATING THE ACTIVE WORKSHEET).

·         PARTS OF THE SCREEN – (TITLE BAR, MENU BAR, STANDARD TOOLBAR, FORMATTING TOOLBAR) (1) FORMULA BAR – PROVIDES INFORMATION ABOUT THE ACTIVE CELL.  FORMULAS CAN BE ENTERED AND EDITED IN THE FORMULA BAR.      (2) SHEET TABS – IDENTIFIES THE CURRENT WORKSHEET.  THE TAB FOR THE ACTIVE WORKSHEET DISPLAYS WITH A WHITE BACKGROUND.  THEY ARE LOCATED AT THE LOWER LEFT HAND SIDE.  (3) WORKSHEET AREA – A COLLECTION OF CELLS WHERE INFORMATION SUCH AS LABELS, VALUES, OR FORMULAS WILL BE ENTERED.  WHEN THE INSERTION POINT (THICK WHITE PLUS SYMBOL) IS POSITION IN A CELL, THE CELL ADDRESS (REFERENCE) DISPLAYS AT THE LEFT SIDE OF THE FORMULA BAR (REFERENCE AREA).

·         THE GRAY VERTICAL AND HORIZONTAL LINES THAT DEFINE THE CELLS IN THE WORKSHEET AREA ARE CALLED GRIDLINES.  WHEN THE INSERTION POINT (APPEARS AS A THICK WHITE PLUS SIGN) IS IN A CELL, THE CELL ADDRESS OR CELL REFERENCE IS DISPLAYED AT THE LEFT SIDE OF THE FORMULA BAR CALLED THE REFERENCE. AREA.  THE CELL REFERENCE IS THE COLUMN LETTER AND THE ROW NUMBER.

·         ENTERING DATA IN A CELL – DATA CAN INCLUDE A LABEL (TITLE), A NUMBER, A VALUE, OR A FORMULA.  TO ENTER DATA, MAKE THE DESIRED CELL ACTIVE (IT IS SURROUNDED BY A THICK BORDER) THEN KEY THE DATA.  TO MOVE TO THE NEXT CELL, PRESS TAB (TO MOVE TO THE RIGHT) OR ENTER (TO MOVE DOWN).  WHEN YOU ARE READY TO KEY DATA INTO THE ACTIVE CELL, CHECK THE STATUS BAR.  THE WORD “READY” SHOULD DISPLAY.  DATA BEING KEYED IN A CELL DISPLAYS IN THE CELL AS WELL AS THE FORMULA BAR.  TEXT WILL “OVERLAP” INTO THE NEXT CELL, NUMBERS WON’T.

·         USING AUTOMATIC ENTERING FEATURES – EXCEL CONTAINS SEVERAL FEATURES THAT HELP YOU ENTER DATA INTO CELLS QUICKLY AND EFFICIENTLY.  THESE FEATURES INCLUDE AUTOCOMPLETE (AUTOMATICALLY INSERTS DATA IN A CELL THAT BEGINS THE SAME AS THE PREVIOUS ENTRY SUCH AS A NAME), AUTOCORRECT (CORRECTS MANY COMMON TYPOGRAPHICAL ERRORS SUCH AS THE OR AND), AND AUTOFILL (AUTOMATICALLY INSERTS WORD, NUMBERS, OR FORMULAS).  WHEN A CELL IS ACTIVE, IT IS SURROUNDED BY A THICK, BLACK BORDER.  A SMALL, BLACK SQUARE IS LOCATED AT THE BOTTOM RIGHT SID OF THIS BORDER.  THIS BLACK SQUARE IS CALLED THE AUTOFILL HANDLE.  WITH THE AUTOFILL HANDLE, YOU CAN QUICKLY FILL A RANGE OF CELLS WITH THE SAME DATA OR CONSECUTIVE DATA (YEARS OR MONTHS IN ORDER OR REPEAT NAMES).  TYPE IN THE FIRST ENTRY, MOVE MOUSE TO LOWER RIGHT AND HOLD LEFT BUTTON AND DRAG.  IF YOU WANT NUMBERS, KEY THE FIRST TWO CELLS, SELECT THEM AND THEN DRAG. 

·         EDITING DATA IN CELLS – DELETE WITH BACKSPACE OR DELETE AS KEYING.  TO CHANGE THE DATA IN A CELL, MAKE THE CELL ACTIVE AND START KEYING.  THE EXISTING TEXT IS DELETED.  IF YOU ONLY WANT TO DELETE A PORTION OF THE TEXT, DOUBLE CLICK ON THE CELL TO PUT IT IN THE EDIT MODE.  WHEN FINISHED, MAKE ANOTHER CELL ACTIVE TO TURN OFF THE EDIT MODE (IT WILL SAY EDIT ON THE STATUS BAR WHEN EDIT MODE IS ON).

·         FUNCTIONS SUCH AS SAVING, OPENING, PRINTING, CLOSING AND EXITING AN EXCEL WORKBOOK IS THE SAME AS IN WORD.

·         SELECTING CELLS – (1) COLUMN – PLACE MOUSE IN COLUMN HEADER (LETTER) AND CLICK (2) ROW – CLICK ON ROW HEADER (NUMBER)  (3) SPECIFIC CELLS – DRAG THROUGH WITH THE MOUSE.  FOR NONADJACENT CELLS, HOLD DOWN THE CTRL KEY AND CLICK WITH THE MOUSE.  SELECTED CELL EXCEPT THE ACTIVE CELL DISPLAY WITH A BLACK BACKGROUND.  (4) ALL CELLS – CLICK ON THE SELECT ALL BUTTON (THE BOX IN THE UPPER CORNER BY A AND 1).

·         FORMATTING WITH AUTOFORMAT – SELECT CELLS (DON’T USE THE SELECT ALL BUTTON), CHOOSE FORMAT-AUTOFORMAT AND CLICK ON CHOICE.

 

CHAPTER 2:  FORMATTING AN EXCEL WORKSHEET

·         PREVIEWING A WORKSHEET – BEFORE PRINTING, IT IS A GOOD IDEA TO PREVIEW A WORKSHEET.  CLICK ON THE PRINT PREVIEW BUTTON ON THE STANDARD TOOLBAR.  BY DEFAULT, THE GRIDLINES DO NOT PRINT.

·         APPLYING CHARACTER FORMATTING WITH THE FORMATTING TOOLBAR – SELECT THE DESIRED CELLS, CHOOSE BOLD, ITALIC, OR UNDERLINE.  YOU CAN ALSO CHANGE THE FONT TYPEFACE, TYPE SIZE, OR FONT COLOR.

·         CHANGING COLUMN WIDTH AND ROW HEIGHT – BY DEFAULT, ALL COLUMN WIDTHS ARE EQUAL AND ALL ROW HEIGHTS ARE EQUAL.  (1) CHANGING COLUMN WIDTH – WITH THE MOUSE, YOU CAN DRAG THE COLUMN BOUNDARIES TO DESIRED LOCATION.  YOU WILL SEE A MEASUREMENT DISPLAYED TO MAKE IT EASY TO MOVE TO A SPECIFIC LOCATION.  YOU CAN SELECT ADJACENT COLUMNS AND CHANGE ALL COLUMNS EQUALLY.  YOU CAN ALSO AUTOMATICALLY ADJUST THE WIDTH OF A COLUMN TO FIT THE LONGEST ENTRY.  POSITION THE MOUSE BETWEEN THE DESIRED COLUMN AND THE NEXT COLUMN BOUNDARY AND DOUBLE CLICK.  YOU AN ALSO CHANGE THE WIDTH BY CHOOSING FORMAT-COLUMN-WIDTH.   (2) CHANGING ROW HEIGHT - THIS IS VERY SIMILAR TO CHANGING COLUMN WIDTH.  PLACE THE MOUSE ON ROW BOUNDARIES AND DRAG. 

·         FORMATTING DATA IN CELLS – BY DEFAULT, WORD AND LETTERS ARE LEFT ALIGNED, NUMBERS ARE RIGHT ALIGNED.

·         FORMATTING NUMBERS – FORMAT SYMBOLS YOU CAN USE TO FORMAT NUMBERS INCLUDE A PERCENT (%), COMMA (,), AND A DOLLAR SIGH ($).  NUMBERS CAN ALSO BE FORMATTED BY CHOOSING FORMAT-CELLS TO DISPLAY THE FORMAT CELLS DIALOG BOX OR ON FORMATTING TOOLBAR.

·         CHANGING DATA ALIGNMENT IN CELLS – YOU CAN CHANGE THE ALIGNMENT WITH FORMATTING TOOLBAR OR FORMAT-CELLS-ALIGNMENT.  FROM HERE, YOU CAN ALSO SPECIFY THE HORIZONTAL AND VERTICAL ALIGNMENT AND THE ORIENTATION.  BY DEFAULT, TEXT OVERLAPS.  TO WORD WRAP, CHOOSE FORMAT-CELL-ALIGNMENT-WRAP.

·         FORMATTING CELLS – (1) ADDING BORDERS TO CELLS BY DEFAULT, THE GRIDLINES DO NOT PRINT.  BORDERS CAN BE ADDED.  SELECT THE CELLS, CLICK ON THE BORDERS BUTTON AND CLICK ON THE DESIRED BORDER STYLE. CHOOSE FORMAT-CELLS TO HAVE MORE CONTROL OVER THE TYPES OF LINES AND BORDERS. (2) ADDING SHADING AND A PATTERN TO CELLS – TO ENHANCE THE VISUAL DISPLAY, CONSIDER ADDING SHADING AND/OR A PATTERN.  FOR SHADING, CHOOSE THE DESIRED CELLS, CLICK ON THE DOWN POINTING ARROW NEXT TO FILL AND CHOOSE COLOR.  FOR PATTERNS, SELECT THE DESIRED CELLS, CHOOSE FORMAT-CELLS-PATTERNS.

·         MAINTENANCE ON A WORKSHEET MAY INCLUDE ADDING OR DELETING ROWS OR COLUMNS, CHANGING MARGINS, PRINT COLUMN AND ROW TITLES, PRINT GRIDLINES, AND CENTER A WORKSHEET HORIZONTALLY OR VERTICALLY.

·         INSERTING/DELETING ROWS AND COLUMNS – (1) INSERTING ROWS – BY DEFAULT, THE ROW IS ADDED (INSERTED) ABOVE THE ROW CONTAINING THE ACTIVE CELL.  TO INSERT A ROW, CHOOSE INSERT-ROW.  (2) INSERTING COLUMNS – CHOOSE INSERT-COLUMN.  BY DEFAULT, COLUMNS ARE INSERTED IMMEDIATELY TO THE LEFT OF THE COLUMN CONTAINING THE ACTIVE CELL.  IF YOU WISH TO INSERT MORE THAN ONE ROW OR COLUMN AT ONCE, BLOCK THE NUMBER OR ROWS OR COLUMNS YOU WISH TO INSERT.  (3) DELETING CELLS, ROWS, OR COLUMNS – TO DELETE A SPECIFIC CELL, MAKE THE DESIRED CELL ACTIVE AND HIT DELETE.  YOU CAN ALSO SELECT A GROUP OF CELLS AND HIT DELETE.  THE DELETE KEY ONLY REMOVES THE CONTENTS OF THE CELLS, NOT THE CELLS THEMSELVES.  IF YOU WANT TO DELETE CELL TEXT AS WELL AS THE CELLS, SELECT THE CELLS, CHOOSE EDIT-DELETE.  AT THE DELETE DIALOG BOX, CHOOSE YOUR DESIRED OPTIONS (SHIFT CELLS UP, TO LEFT, TO RIGHT…).  THE DELETE DIALOG BOX CAN ALSO BE ACCESSED FROM THE SHORTCUT MENU.  (4) CLEARING DATA IN CELLS – THE CLEAR-ALL OPTION FROM THE EDIT MENU CAN BE USED TO CLEAR SELECTED CELLS OF TEXT AND FORMATTING BUT LEAVES THE CELLS.  THE CLEAR-FORMATS REMOVES ANY FORMATTING BUT LEAVES THE TEXT.  THE CLEAR-CONTENTS LEAVES FORMAT BUT CLEARS TEXT.

 

CHAPTER 3:  INSERTING FORMULAS IN A WORKSHEET

·         A FORMULA CONTAINS A MATHEMATICAL OPERATOR, VALUE, CELL REFERENCE, CELL RANGE, AND/OR A FUNCTION.  FORMULAS CAN BE WRITTEN TO HANDLE ALMOST ANY MATHEMATICAL OPERATION.

·         USING THE AUTOSUM BUTTON – TO PERFORM A CALCULATION IN A WORKSHEET, MAKE THE CELL ACTIVE IN WHICH YOU WANT TO INSERT THE FORMULA (IT SHOULD BE EMPTY).  KEY THE FORMULA IN.  IT WILL DISPLAY IN THE FORMULA BAR AS YOU KEY.  ONE OF THE ADVANTAGES OF USING FORMULAS IS THAT CELL ENTRIES CAN BE CHANGED AND THE FORMULA WILL AUTOMATICALLY RECALCULATE THE VALUES AND INSERT THE RESULT IN THE CELL CONTAINING THE FORMULA.  IN ADDITION TO KEYING FORMULAS, YOU CAN USE THE AUTOSUM BUTTON. (3) EXCEL LOOKS FOR A RANGE OF CELLS ABOVE AND THEN TO THE LEFT OF THE ACTIVE CELL AND GIVES A SUGGESTION.  IF IT IS CORRECT, HIT ENTER.  IF NOT, DRAG THROUGH THE RANGE, THEN HIT ENTER.  YOU CAN DOUBLE CLICK TO ACCEPT THE RANGE.

·         WRITING FORMULAS WITH MATHEMATICAL OPERATORS – WHEN WRITING YOUR OWN FORMULA, BEGIN THE FORMULA WITH THE EQUALS (=) SIGN.  SOME FORMULAS CAN ALSO BEGIN WITH THE MINUS (-) SIGN OR PLUS (+) SIGN.  IF THERE ARE ONE OR MORE OPERATORS IN A FORMULA, EXCEL USES THE SAME ORDER OF OPERATIONS USED IN ALGEBRA.  YOU CAN USE PARENTHESES TO CHANGE THE ORDER.                   (1) COPYING A FORMULA – WHEN A RELATIVE VERSION OF A FORMULA IS COPIED, THE CELLS INCLUDED IN THE FORMULA CHANGE.  SELECT THE CELL WITH THE FORMULA PLUS THE CELLS TO COY IT TO , CHOOSE EDIT-FILL AND MAKE SELECTION TO COPY TO (LEFT, RIGHT, UP, DOWN…).  (2) COPYING FORMULAS WITH AUTOFILL – PLACE A FORMULA IN A CELL, WITH THAT CELL ACTIVE, DRAG THE CORNER THROUGH THE DESIRED CELLS. 

·         INSERTING A FORMULA WITH THE INSERT FUNCTION BUTTON ON THE FORMULA BAR -  =SUM IS CALLED A FUNCTION.  A FUNCTION IS A BUILT IN FORMULA.  A FUNCTION OPERATES ON WHAT IS REFERRED TO AS AN ARGUMENT.  AN ARGUMENT MAY CONSIST OF A CONSTANT, A CELL REFERENCE, OR ANOTHER FUNCTION CALLED A NESTED FUNCTION.  EX. =SUM (B3:B4) IS A REFERENCE ARGUMENT.  EX. =SUM (B3:B9, 100) IS A REFERENCE ARGUMENT WITH A CONSTANT.  WHEN YOU CLICK ON THE PAST FUNCTION (fx) BUTTON OR INSERT-FUNCTION, THE PAST FUNCTION DIALOG BOX APPEARS.  AT THIS BOX, FUNCTION CATEGORIES DISPLAY ON LEFT, FUNCTION NAMES ON THE RIGHT.  CHOOSE A FUNCTION NAME-OK.  THEN A THE NEXT BOX, A RANGE IS SUGGESTED.  YOU CAN CHANGE THE RANGE IF YOU WISH.  (1) FINDING AVERAGES – A COMMON FUNCTION IN A FORMULA IS THE AVERAGE FUNCTION.  YOU CAN USE THE PASTE FUNCTION.  THE AVERAGE FUNCTION WILL IGNORE TEXT AND BLANK CELLS.             (2) FINDING MAXIMUM AND MINIMUM VALUES – THE MAX FUNCTION IS USED TO RETURN THE MAXIMUM VALUE INSIDE A RANGE (MIN FINDS MINIMUM).  USE THE PASTE FUNCTION. 

 

 

CHAPTER 4:  FORMATTING A WORKSHEET

·         FORMATTING A WORKSHEET PAGE – THE DEFAULT PAGE IS PORTRAIT, 1 INCH TOP AND BOTTOM MARGINS, .75 LEFT AND RIGHT MARGINS.  (1) CONTROLLING THE PAGE LAYOUT – THE PAGE LAYOUT DIALOG BOX DISPLAYS OPTIONS FOR CONTROLLING THE LAYOUT OF THE WORKSHEET.  CHOOSE FILE-PAGE SETUP (PAGE TAB)-ORIENTATION CONTROLS HOW THE PAGE IS PRINTED.  THE OTHER OPTION IS LANDSCAPE.  (2) SCALING – YOU CAN ADJUST THE SIZE OF THE DATA BY PERCENTAGE.   YOU CAN SPECIFY ON HOW MANY PAGES YOU WANT THE DATA TO FIT ON.  CHOOSE FILE-PAGE SETUP (PAGE TAB).  (3) PRINT QUALITY – DEPENDS UPON THE PRINTER.  (4) FIRST PAGE NUMBER  - USED TO SPECIFY STARTING NUMBER.  CHOOSE FILE-PAGE SETUP (HEADER/FOOTER AND CREATE IT).  (5) TURNING ON PAGE NUMBERING  - BY DEFAULT, WORKSHEET PAGES AREN’T NUMBERED.  FROM PAGE SETUP, CHOOSE THE HEADER/FOOTER TAB.  TO INSERT PAGE NUMBERING, CLICK ON CUSTOM HEADER OR CUSTOM FOOTER.

·         CHANGING WORKSHEET MARGINS – CHOOSE FILE PAGE- SETUP (MARGIN TAB). YOU WILL SEE A REVIEW OF THE WORKSHEET.  CLICK ON THE SPIN BOXES TO MAKE CHANGES.

·         CENTERING A WORKSHEET HORIZONTALLY AND/OR VERTICALLY – MANY WORKSHEETS ARE SMALL AND ARE PRINTED IN THE UPPER LEFT HAND CORNER.  CHOOSE FILE-PAGE SETUP (MARGINS TAB) AND CLICK IN THE BOX TO CENTER VERTICALLY OR HORIZONTALLY.

·         INSERTING PAGE BREAK – BY DEFAULT, YOU CAN HAVE 7 INCHES ACROSS A PAGE.  A BREAK IS INSERTED (DASHED VERTICAL LINE).  AFTER 9 INCHES VERTICALLY, A PAGE BREAK IS INSERTED (DASHED HORIZONTAL LINE).  EXCEL WILL AUTOMATICALLY INSERT PAGE BREAKS.  YOU CAN INSERT A PAGE BREAK.  SELECT THE COLUMN OR ROW THEN CHOOSE INSERT-PAGE BREAK.  THE BREAK IS PLACED TO THE LEFT OF THE SELECTED COLUMN OR ABOVE THE SELECTED ROW.  TO INSERT A VERTICAL AND HORIZONTAL PAGE BREAK, MAKE THE CELL ACTIVE AND CHOOSE INSERT-PAGE BREAK.

·         PRINTING COLUMN AND ROW TITLES ON MULTIPLE PAGES – COLUMNS ARE ROWS ARE USUALLY TITLES.  SOMETIMES IT WOULD BE BENEFICIAL TO PRINT THESE HEADINGS ON ALL PAGES.  TO DO THIS, CHOOSE FILE-PAGE SETUP (SHEET TAB), CLICK ON ROW AND COLUMN HEADINGS UNDER PRINT BOX.  UNDER PRINT TITLES, KEY IN THE ROWS AND/OR COLUMNS TO REPEAT.

·         PRINTING GRIDLINES – CHOOSE FILE-PAGE SETUP (SHEET TAB), CLICK ON GRIDLINES UNDER PRINT.

·         COMPLETING A SPELL CHECK – MAKE THE FIRST CELL ACTIVE, CLICK ON SPELLING BUTTON ON THE STANDARD TOOLBAR.

·         USING UNDO AND REDO BUTTONS – CLICK ON UNDO OR REDO ON STANDARD TOOLBAR

·         FINDING AND REPLACING DATA IN A WORKSHEET – THIS FEATURE ALLOWS YOU TO FIND CERTAIN DATA AND REPLACE IT WITH SPECIFIC TEX OR NOTHING.  CHOOSE EDIT-REPLACE.  AT THE REPLACE DIALOG BOX, KEY IN DATA YOU ARE LOOKING FOR AND WHAT YOU WANT TO REPLACE IT WITH.  CLICK ON FIND NEXT TO FIND THE NEXT OCCURRENCE OF THE DATA.  CLICK THE REPLACE BUTTON IF YOU WISH TO REPLACE OR FIND NEXT TO LEAVE IT THERE ARE FIND THE NEXT OCCURRENCE.  CLICK ON REPLACE ALL IF YOU KNOW YOU WILL BE REPLACING EVERY OCCURRENCE.  TO MATCH EXACT CASE, CHOOSE THIS OPTION.  IF YOU WANT TO FIND CELLS THAT ONLY CONTAIN THE DATA, NOT A PORTION, CHOOSE FIND ENTIRE CELLS.  ONLY (0% WILL STOP ON 90% UNLESS THIS IS CHECKED)

·         SORTING DATA – EXCEL CONTAINS SOME BASIC DATABASE FUNCTIONS.  YOU CAN SORT NUMERICALLY OR ALPHABETICALLY. YOU CAN SORT ASCENDING OR DESCENDING.  SELECT THE CELLS CONTAINING DATA YOU WANT OT SORT, THEN CLICK SORT ASCENDING (A-Z) OR SORT DESCENDING (Z-A) BUTTON.  IF THE DATA IS RELATED TO OTHER COLUMNS, SELECT THE COLUMN, CHOOSE TABLE SORT AND ANSWER THE QUESTION.  IF MORE THAN ONE COLUMN IS SELECTED, EXCEL SORTS BY FIRST COLUMN.

·         FILTERING LISTS – YOU CAN PLACE A RESTRICTION, CALLED A FILTER, ON DATA INA WORKSHEET TO ISOLATE TEMPORARILY A SPECIFIC LIST.  BE IN A CELL THAT MATCHES CRITERIA, CHOOSE DATA-FILTER-AUTOFILTER.  A DROP-DOWN LIST WILL APPEAR WILL ALL POSSIBLE CHOICES.  CLICK ON THE CHOICE YOU WANT TO FILTER BY. 

 

CHAPTER 5: MOVING DATA WITHIN AND BETWEEN WORKBOOKS

 

Ψ       MOVING, COPYING, AND PASTING CELLS – SITUATIONS MAY OCCUR WHERE YOU NEED TO MOVE CELLS TO A DIFFERENT LOCATION OR MAY NEED TO COPY REPETITIVE DATA IN A WORKSHEET. (1) MOVING SELECTED CELLS – SELECTED CELLS CAN BE MOVED WITH THE CUT AND PASTE BUTTONS ON THE STANDARD TOOLBAR, BY DRAGGING WITH THE MOUSE, OR WITH OPTIONS ON THE EDIT DROP DOWN MENU. WITH THE TOOLBAR, SELECT THE CELLS, CLICK THE CUT BUTTON. THIS CAUSES A DASHED LINE TO BE PLACED AROUND THE SELECTED CELLS. CLICK THE CELL WHERE YOU WANT THE FIRST SELECTED CELL TO BE PLACED AND THEN CLICK THE PASTE BUTTON OR HIT ENTER. WITH THE MOUSE, SELECT THE CELLS, POSITION THE MOUSE POINTER ON ONE OF THE BORDERS UNTIL IT TURNS INTO AN ARROW POINTER, HOLD DOWN THE LEFT BUTTON, DRAG OUTLINE OF SELECTED CELLS TO NEW LOCATIONS, RELEASE. (2) COPY SELECTED CELLS – SELECT DESIRED CELLS, CLICK ON COPY BUTTON, CLICK IN THE FIRST CELL FOR NEW LOCATION, CLICK ON PASTE. WITH THE MOUSE, HOLD DOWN THE CTRL KEY ALONG WITH THE MOUSE BUTTON AND DRAG TO NEW LOCATION.

Ψ       CREATING A WORKBOOK WITH MULTIPLE WORKSHEETS – UP TO THIS POINT, EACH WORKBOOK HAS CONTAINED ONLY ONE WORKSHEET. A WORKBOOK CAN CONTAIN FROM 1 TO 255 WORKSHEETS. YOU WOULD CREATE A VARIETY OF WORKSHEETS WITHIN A WORKBOOK FOR RELATED DATA. THE COPY AND PASTE FEATURES CAN BE USEFUL IN CREATING MORE THAN ONE WORKSHEET WITHIN A WORKBOOK, ESPECIALLY IF THERE IS SOME CONSISTENCY WITH THE DATA. IF YOU NEED MORE THAN THE VISIBLE 3 TABS FOR ANOTHER WORKSHEET, CHOOSE INSERT- WORKSHEET.

Ψ       PRINTING A WORKBOOK CONTAINING NUMEROUS WORKSHEETS – WHEN PRINTING, EXCEL WILL PRINT THE DISPLAYED WORKSHEET. TO PRINT ALL THE WORKSHEETS, CHOOSE FILE-PRINT-PRINT WHAT AND CHOOSE ENTIRE WORKBOOK OR HOLD DOWN SHIFT KEY AND CLICK ON DESIRED TABS AT BOTTOM.

Ψ       DELETING A WORKSHEET – POSITION MOUSE ON TAB OF DESIRED WORKSHEET TO DELETE, RIGHT CLICK AND CHOOSE DELETE.

Ψ       SPLITTING A WORKSHEET INTO WINDOWPANES AND FREEZING PANES -  IN SOME WORKSHEETS, NOT ALL CELLS DISPLAY AT ONE TIME IN THE WORKSHEET AREA. YOU MAY FIND SPLITTING THE WORKSHEET WINDOW INTO PANES HELPFUL. CHOOSE WINDOW-SPLIT OR USE THE VERTICAL AND/OR HORIZONTAL SPLIT BARS AT THE TOP OF THE  VERTICAL SCROLL BAR AND AT THE RIGHT OF THE HORIZONTAL SCROLL BAR. YOU CAN ADJUST THE SPLIT BY PLACING THE MOUSE ON ONE OF THE LINES, TURNING IT INTO A SYMBOL AND DRAG TO RESIZE. YOU CAN FREEZE PANES IN ORDER FOR CERTAIN TEXT TO REMAIN VISIBLE. SPLIT THE WINDOWPANES AND THEN MOVE THE LINES TO LOCATION TO FREEZE AND CHOOSE WINDOW-FREEZE PANES.

Ψ       WORKING WITH WINDOWS – (1) OPENING MULTIPLE WORKBOOKS – IF YOU OPEN A WORKBOOK WHILE ANOTHER IS OPEN, IT IS PLACED ON TOP OF IT. NOTHING IS CHANGED. YOU CAN OPEN MORE THAN ONE WORKBOOK AT A TIME. AT THE OPEN DIALOG BOX, HOLD DOWN THE  CTRL KEY AND CLICK ON THE DESIRED WORKBOOKS THEN OPEN. TO SEE WHAT WORKBOOKS ARE OPEN, CHOOSE WINDOW AND LOOK AT THE LIST AT THE BOTTOM. THE 4 SHOWS THE ACTIVE WORKBOOK.      (2) CLOSING MULTIPLE WORKBOOKS – ALL OPEN WORKBOOKS CAN BE CLOSED AT ONCE. TO DO THIS, HOLD DOWN SHIFT KEY, CHOOSE FILE-CLOSE ALL. (3) ARRANGING WORKBOOKS – IF YOU HAVE MORE THAN ONE WORKBOOK OPEN, YOU CAN ARRANGE THEM WITH WINDOW-ARRANGE. AT THE ARRANGE DIALOG BOX, CHOOSE TILED TO DISPLAY A PORTION OF EACH OPEN WORKBOOK. CHOOSE THE HORIZONTAL OPTION TO DISPLAY EACH GOING ACROSS. CHOOSE VERTICAL FOR UP AND DOWN. CHOOSE CASCADE TO DISPLAY ONE WORKBOOK AND THE REST AS TITLE BARS. (4) SIZING WORKBOOKS – THE MAXIMIZE AND MINIMIZE BUTTONS IN THE UPPER RIGHT HAND CORNER OF THE ACTIVE WORKBOOK CAN BE USED TO CHANGE THE SIZE OF THE WINDOW.

Ψ       MOVING, COPYING AND PASTING DATA BETWEEN WORKBOOKS – WITH MORE THAN ONE WORKBOOK OPEN, YOU CAN MOVE, COPY AND/OR PASTE DATA FROM ONE TO ANOTHER. USE THE CUTTING, COPYING AND PASTING CONCEPTS.

Ψ       COPYING AND PASTING A WORKSHEET BETWEEN PROGRAMS – MS OFFICE  IS A SUITE THAT ALLOWS INTEGRATION. THIS MEANS COMBINING DATA FROM 2 OR MORE PROGRAMS INTO ONE DOCUMENT.

Ψ       TO COPY AND EXCEL WORKBOOK INTO A WORD DOCUMENT, HAVE BOTH PROGRAMS OPEN. BLOCK AND COPY DESIRED EXCEL WORKBOOK, MAXIMIZE WORD AND MOVE INSERTION POINT TO DESIRED LOCATION FOR WORKBOOK IN THE WORD SCREEN AND CLICK ON PASTE. 

Ψ       LINKING CELLS BETWEEN WORKSHEETS – IN WORKBOOKS CONTAINING MULTIPLE WORKSHEETS OR BETWEEN RELATED WORKBOOKS, THERE MAY BE DATA IN CELLS THAT CREATE A LINK BETWEEN WORKSHEETS OR WORKBOOKS.  A CHANGE MADE IN A LINKED CELL IS AUTOMATICALLY MADE TO THE OTHER CELLS IN THE LINK.  LINKS CAN BE MADE WITH INDIVIDUAL CELLS OR WITH A RANGE OF CELLS.  THIS TYPE OF LINK IS CALLED A DYNAMIC LINK.  THE WORKSHEET WITH THE ORIGINAL DATA IS CALLED THE SOURCE AND THE RELYING WORKSHEET IS CALLED THE DEPENDENT.  TO CRATE A LINK, SELECT THE CELL CONTAINING DATA TO BE LINKED, CLICK ON COPY, MAKE ACTIVE THE WORKSHEET WHERE YOU WANT TO PAST THE CELL(S) AND THEN CLICK EDIT-PASTE SPECIAL.  AT THE PASTE SPECIAL DIALOG BOX, SPECIFY WHAT IN THE CELL YOU WANT TO COPY AND WHAT OPERATORS YOU WANT TO INCLUDE. 

Ψ       USING EXCEL TEMPLATES – EXCEL HAS INCLUDED A NUMBER OF TEMPLATE WORKSHEET FORMS THAT ARE FORMATTED FOR SPECIFIC USES SUCH AS EXPENSE STATEMENTS, INVOICES AND PURCHASE ORDERS. TO DISPLAY THE LIST, CHOOSE FILE-NEW AND MAKE SURE THE SPREADSHEET SOLUTIONS TAB IS SELECTED. CLICK ON DESIRED TEMPLATE AND CHOOSE OPEN.

 

CHAPTER 7: CREATING A CHART IN EXCEL

·         IN EXCEL, TO CREATE A CHART SELECT THE CELLS CONTAINING THE DATA YOU WANT TO GRAPH, THEN CLICKING ON THE CHART WIZARD CAN CREATE A CHART.  THERE ARE 4 STEPS INVOLVED IN CREATING A CHART WITH THE CHART WIZARD.      (1) CHOOSE THE DESIRED CHART TYPE AND CHART SUB-TYPE AT THE CHART TYPE DIALOG BOX.  CLICK NEXT.  (2) MAKE SURE THE DATA RANGE DISPLAYS CORRECTLY AT THE CHART SOURCE DATA DIALOG BOX.  CLICK NEXT.  (3) MAKE ANY CHANGES TO THE CHART AT THE CHART OPTIONS DIALOG BOX.    CLICK NEXT.  SPECIFY WHERE YOU WANT THE CHART INSERTED AT THE CHART LOCATION DIALOG BOX.  CLICK FINISH.

·         THE VERTICAL AXIS IS THE Y-AXIS.  THE HORIZONTAL AXIS IS THE X-AXIS.  THE Y-AXIS CONTAINS TICK MARKS.

·         TO PRINT JUST THE CHART IN A WORKSHEET, SELECT THE CHART, CHOOSE FILE-PRINT, MAKE SURE THE SELECTED CHART OPTION IS CHOSEN, CLICK ON PRINT.

·         CREATING A CHART IN A SEPARATE WORKSHEET – IF A CHART IS INSERTED I THE SAME WORKSHEET AS THE CELLS CONTAINING THE DATA, YOU CANNOT DELETE THE DATA TO DISPLAY ONLY THE CHART BECAUSE THE DATA IN THE CHART WILL ALSO BE DELETED.  IF YOU WANT TO CREATE A CHART IN A WORKSHEET BY ITSELF, CLICK THE “AS NEW SHEET” OPTION AT THE CHART WIZARD.  YOU WILL SEE THE TABS AT THE BOTTOM DISPLAY CHART 1 AND SHEET 1.  CLICK ON ONE TO MOVE TO IT.

·         DELETING A CHART – CLICK ONCE ON THE CHART TO SELECT IT.  PRESS DELETE.

·         SIZING AND MOVING CHARTS – THE SIZE OF A CHART CAN BE CHANGED.  CLICK ON THE CHART TO SELECT IT.  NOTICE THE SIZING HANDLES.  DRAG THE SIZING HANDLES TO RESIZE.  TO MOVE A CHART, SELECT IT AND DRAG IT WITH THE MOUSE.

·         CHANGING THE CHART TYPE – CHOSEN IN STEP 1 OF THE CHART WIZARD OR CAN BE CHANGED FOR A EXISTING CHART.  MAKE SURE CHART IS SELECTED, CHOOSE CHART-CHART TYPE-MAKE SELECTION.

·         CHOOSING A CUSTOM CHART – THE CHART FEATURE OFFERS A VARIETY OF PREFORMATTED CUSTOM CHARTS.  A CUSTOM CHART CAN BE CHOSEN IN STEP 1 OR YOU CAN APPLY A CUSTOM CHART TYPE TO AN EXISTING CHART IN A SIMILAR FASHION AS CHANGING THE CHART TYPE.

·         CHANGING DATA IN CELLS – THE CHART FEATURE USES DATA IN CELLS TO CREATE A CHART.  THIS DATA CAN BE CHANGED AND THE CHART WILL REFLECT THE CHANGES.

·         CHANGING THE DATA SERIES – WHEN A CHART IS CREATED, THE CHART WIZARD USES THE DATA IN THE FIRST ROW (EXCEPT THE FIRST CELL) T CREATE THE X-AXIS AND USES THE DATA IN THE FIRST COLUMN (EXCEPT THE FIRST CELL) AS THE LEGEND.  WHEN A CHART IS CREATED, THE OPTION ROWS IS SELECTED BYE DEFAULT IN STEP 2.  YOU CAN SWITCH IT TO COLUMNS.  CHOOSE CHART-SOURCE DATA-MAKE CHANGE.

·         ADDING CHART ELEMENTS – WHEN A CHART IS CREATED, A CHART LEGEND AND LABELS FOR THE X AND Y-AXES ARE PROVIDED.  OTHER CHART ELEMENTS CAN BE ADDED SUCH AS A TITLE AND DATA LABELS.  THESE ELEMENTS CAN BE ADDED IN STEP 3 OR TO AN EXISTING CHART BY SELECTING THE CHART AND CHOOSING CHART-CHART OPTIONS..

·         MOVING/SIZING CHART ELEMENTS – WHEN ADDITIONAL ELEMENTS ARE ADDED TO A CHART, THE CHART CAN BECOME QUITE FULL AND ELEMENTS MAY OVERLAP.  AN ELEMENT CAN BE SELECTED AND MOVED SELECT ELEMENT AND DRAG.

·         DELETING/REMOVING CHART ELEMENTS – SELECT THE ELEMENT AND PRESS DELETE.

·         ADDING GRIDLINES – GRIDLINES CAN BE ADDED TO A CHART FOR THE CATEGORY, SERIES, AND VALUE.  TO ADD GRIDLINES, DISPLAY THE CHART OPTIONS DIALOG BOX AND THEN CLICK THE GRIDLINES TAB AND MAKE SELECTION.

·         FORMATTING CHART ELEMENTS – DOUBLE CLICK IN THE CHART AREA TO DISPLAY THE FORMAT CHART AREA.  YOU CAN CHANGE THE TYPEFACE AND TYPE SIZE. YOU CAN ALSO CHANGE THE BACKGROUND COLOR AND PATTERN.  DOUBLE CLICK ON AN ELEMENT TO DISPLAY THIS BOX.

·         CHANGING ELEMENT COLORS – A FILL COLOR CAN BE ADDED TO A CHART OR CHART ELEMENT.  DOUBLE CLICK ON THE DESIRED ELEMENT-CHOOSE PATTERNS-CHOOSE COLOR.