Download COMPUTER FUNDAMENTALS - LECTURE NOTES and more Lecture notes Computer Science in PDF only on Docsity!
Introduction to Microsoft Excel
WHAT IS A SPREADSHEET
A spreadsheet is used to carry out everything from simple addition to complex financial and statistical analyses.
Spreadsheet programs can also help you create charts and graphs based on the data you’ve entered.
Brand Names of Spreadsheet Software
- Microsoft Excel
- Microsoft Works Spreadsheet
- Lotus 1-2-
BASIC EXCEL SKILLS
Starting Up Microsoft Excel
- Click on START, choose PROGRAMS and click on Microsoft Excel
- You will see a white grid in front of you with rows of menus and icons at the top of the screen.
The Key Components of a Spreadsheet
- Title Bar: The bar that displays the name of the spreadsheet you currently have open.
- Name Box : The box that displays the address of the cell that you are presently on.
- Formula Box: The bar where the contents of the cell are displayed.
- Worksheet Tabs: These tabs allow you to switch from one worksheet to another. The standard spreadsheet contains three worksheets.
Entering Data into a Spreadsheet
- To enter data into a cell, simply click into the cell and type.
- ACTIVITY :
2..aStart in cell A1, and type “Day of the Week”. Press Enter, or click your mouse into cell A2 and type “Monday”. Continue down Column A until you’ve typed Monday through Sunday. 2..b Click on cell B1 and type “Customers”. Proceed to cells B2-B8 begin typing in the numbers: 20, 10, 30, 50, 60, 100, 70.
Entering a Formula into a Spreadsheet
- What is a formula? It is a command that instructs Excel to carry out a calculation. Addition, subtraction and averaging are all examples.
- ACTIVITY: 3..aIn cell A9, type “Total”, then, 3..b Move to cell B9 and type “=SUM(B2:B8)” and then press ENTER. You should see a total appear in cell B9.
Calculating Four Basic Math Operations
Addition:
- Use the SUM command, which is =SUM(beginning cell address : ending cell address) and press ENTER. OR,
- If the numbers are not adjacent to each other, you can use the “+” operator as follows: = first cell to be added + second cell to be added + third cell to be added, and so on. Then press ENTER.
Subtraction
- Use the – operator = first cell – second cell and then press ENTER
Multiplication
- Use the * operator = first cell * second cell and then press ENTER
Division
- Use the / operator = first cell / second cell and then press ENTER.
Making Changes to the Contents of a Spreadsheet Cell
ACTIVITY: We made a mistake calculating Tuesday’s customers. There were actually 16 customers, not 10!
- Click on the cell whose contents you would like to change
- (^) See the present contents of the cell appear in the FORMULA Bar and change the data.
- Notice that the TOTAL was automatically recalculated (It now reads 346 instead of 340!)
Saving Your Spreadsheet
- Click on FILE and choose SAVE AS if you are saving the spreadsheet for the first time, otherwise, choose SAVE.
3..b SHIFT CELLS DOWN means that when you insert the new cell, the existing cell will be shifted down. 3..cThe other two choices, ENTIRE ROW, and ENTIRE column are alternative ways to inserting rows and columns.
Copy and Pasting
In Excel, you use the Copy and Paste feature when you want to duplicate a cell or block of cells in another
location within the spreadsheet. The original cell will remain in the original location, but a second copy is found at the new location. ACTIVITY: The regional manager wants a total number of visitors for the week. We can copy the formula from C9 into box B9.
- Click on C9 (the source cell).
- Click on EDIT and choose COPY.
- (^) Click on cell B9 (the destination cell) and click on EDIT and choose PASTE.
- See the total appear.
- NOTE Excel converts the formula’s column “C” references into column “B” references. To prevent this automatic adjustment, which is called Relative Cell Referencing, place $ signs in front of the column and/or row reference that you don’t want Excel to adjust when that cell is copied to another location. For example, if C9 contained “=SUM($C$2:$C$8)”, when it is copied to B9, it will still read “=SUM($C$2:$C$8)”. When Excel’s relative cell referencing is blocked by $ signs, it is known as Absolute Cell Referencing.
- With Excel 2007, use the CLIPBOARD quick menu to access copy and paste features. You may access the copy and paste options by clicking the right mouse button to bring up the quick menu.
Cutting and Pasting
Use this function when you want to move a cell or block of cells from one location to another. No trace of the cell(s) will remain in the original location.
- Click on the cell(s) you would like to cut (the source cell).
- Click on EDIT and choose CUT.
- Click on the cell where you want this cell to reappear (the destination cell) and click on EDIT and PASTE.
- See the cut-cell reappear in the new location.
- With Excel 2007, use the CLIPBOARD quick menu to access copy and paste features. You may access the copy and paste options by clicking the right mouse button to bring up the quick menu.
INTERMEDIATE EXCEL SKILLS: FORMATTING A SPREADSHEET
Formatting a Cell
- You must highlight the cell or block of cells you want to format.
- After you highlight the cells, choose FORMAT from the menu and select CELLS to see your choices. 2..a Number : Allows you to select the format for the type of data within the cells. For example, choose CURRENCY if you are using dollar values, and Excel will add a $ sign in front of the cells. 2..b Alignment : Allows you to control the positioning of the text or number in your cell. If you have a lot of text in one cell, and you don’t want to expand the cell’s width, you can choose WRAP TEXT. 2..c Font : You can change the style and size of the highlighted text or numbers, including making them bold, underlined or italic. This is the same as the Microsoft Word formatting.
2..d Border : Allows you to add vertical or horizontal lines to different parts of the worksheet. You can choose where you’d like the grid lines to print out, the line style, and even the line color. 2..e Patterns : Allows you to control the background color of the highlighted cells and to add patterns. Use this to visually isolate data. 2..f (^) Protection : Allows you to lock cells and hide formulae from careless or unauthorized users. Remember, if you password protect your spreadsheet, make sure you remember the password! 2..g In Excel 2007, you can access most of these features from the quick menus under the HOME tab. To access the features for border, pattern, and protection, click the small arrow from the FONT, ALIGNMENT, or NUMBER quick menus to access these and other formatting features. ACTIVITY: Give row A the following characteristics: bold, underlined and 16 font.
Format the Height of a Row
- Click on any cell in that row and click on FORMAT and choose ROW.
- (^) Click on HEIGHT and type in a new number for the Row Height.
- In Excel 2007, select a row and right click to access the quick menu where you will find the row height feature or under the CELLS quick menu click the word FORMAT Note: The standard setting is 12.75.
Format the Width of a Column
- Click on any cell in that column and click on FORMAT and choose COLUMN.
- Click on WIDTH and type a new number for the Column Width.
- In Excel 2007, select a row and right click to access the quick menu where you will find the column width feature or under the CELLS quick menu click the word FORMAT Note: The standard setting is 8.43.
ACTIVITY: Adjust the column width so that cell A1’s “Days of the Week” label is entirely viewable. If you don’t want to guess at the appropriate Column Width, then select AUTOFIT SELECTION instead and Excel will decide for you.
Format A Sheet Formatting a sheet lets you do two things:
- Change the name of the sheet: 1..aClick on the tab you would like to rename. 1..b (^) Click FORMAT select SHEET (in and click on RENAME. 1..cYou will see the name of the sheet darken. Type in a new name and press Enter. 1..d In Excel 2007, click FORMAT under the CELLS quick menu, then select RENAME SHEET
- Change the Background Image 2..aClick on the tab whose background you would like to change. 2..b Click FORMAT, select SHEET and click on BACKGROUND. 2..cLocate the image file and click INSERT. 2..d In Excel 207, select the PAGE LAYOUT tab, then click on the BACKGROUND option to access this feature
INTERMEDIATE EXCEL SKILLS: CHARTS
ACTIVITY: We want to impress our regional manager with graphically displayed data.
- Highlight your spreadsheet, including the column headers. (Days of the Week, Visitors, etc.)
- Click on INSERT and choose CHART. (In Excel 2007, click on the INSERT tab, and then select the type of chart you would like to create from the options provided – the rest of the instructions below
- Highlight the data, including the titles, but excluding the “Total” and “Daily Averages” rows.
- Click on DATA and then SORT.
- Click on the drop-down arrow below the “Sort By” header and select the criteria by which you want to re-sort your data.
- (^) Click either “Ascending” or “Descending”.
- Click OK.
- For Excel 2007, click the DATA tab, and use the SORT quick menu options to perform various sort types NOTE re-sorting the data by “Days of the Week” in ascending order will not return the data to a Monday – Friday order. It will re-sort the data based on the alphabetical order !! To get it back to Monday-Sunday, click on EDIT and UNDO SORT.
To Keep Row and Column Labels Visible As You Scroll
- (^) To freeze the tope horizontal pane, select the row below where you want the split to appear.
- To freeze the left vertical pane, select the column to the right of where you want the split to appear.
- Click on WINDOWS menu and choose FREEZE PANES.
- To UNDO, return to the WINDOWS menu and choose U NFREEZE PANES.
- For Excel 2007, to freeze panes you would choose the VIEW tab, and then click on word FREEZE PANES.
Filtering Out Data ACTIVITY: The regional manager wants just the data for Saturdays.
- Click anywhere within your data.
- (^) Click on DATA FILTER and then AUTOFILTER
- Click on one of the drop-down arrows and select the criteria you would like to filter the data through.
- Excel pulls out just the data you requested.
- To get rid of the filter, 6..aClick on DATA, click on FILTER and choose AUTOFILTER.
- With Excel 2007, the easiest way to perform filtering is by selecting the data to be filtered, clicking on the DATA tab, then click FILTER under the SORT and FILTER option
