MICROSOFT EXCEL

MICROSOFT EXCEL

Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms, especially since version 5 in 1993, and it has replaced Lotus 1-2-3 as the industry standard for spreadsheets. Excel forms part of Microsoft Office.

 

INTRODUCTION TO MS EXCEL 2007 

Microsoft Excel is a very powerful tool for you to use for numeric computations and analysis. Excel can also function as a simple database but that is another class. Today we will look at how to get starting with Excel and show you around the neighborhood sort of speak. I hope to see you in one of the intermediate classes later on. 

What do I see first?
When you start up Excel, the first thing that you see is this:

This is a workbook. A workbook is a collection of worksheets (spreadsheets) and macros. By default, Excel creates 3 worksheets in a new workbook. The worksheets are designated at the bottom part of the window where you see the file folder-like tabs. The tabs are named Sheet1, Sheet2, and Sheet3. If you click on Sheet2, you will be in Sheet2 and not Sheet1 so you need to be aware of which worksheet you are in.

Exercise 1: renaming a worksheet
I suspect that you will want to rename the worksheet(s) to something more relevant to your needs. To rename a worksheet:

1.            Double click on the sheet’s tab (notice the name becomes highlighted)

2.            Type in the new name 

3.            Hit enter to complete the rename process 

Office Fluent user interface
In Excel 2007, the new Office Fluent user interface replaces the traditional menus and toolbars from previous versions of Excel with a single mechanism designed to help users find the right features more efficiently. The interface contains three main components; The Office Button, The Quick Access Toolbar, and The Ribbon. 

Exercise 2: customize the Quick Access Toolbar 
By default, the toolbar only contains three basic functions; save, undo and redo. You can customize the toolbar to include a shortcut to any pre-defined function in Excel.

1.            Right click within the Quick Access Toolbar, and click on Customize Quick Access Toolbar

2.            Click on the name of a function to highlight it, then click Add.

3.            Click on OK when done. 

Exercise 3: Open a New Workbook
Some times you have to create a new workbook. 

1.            Click on the Office Button

2.            Click on New 

You are now able to open recently used or new workbooks from this panel.  You can also open up templates that are available with Excel or ones that you create. If you click on Blank Workbook, Excel will create a new workbook for you. 

If you click on Installed Templates, you can select already made workbooks that Microsoft provides. For example, there is a Personal Monthly Budget. 

(You could also open a new workbook by hitting CTRL-N, or by adding New to the Quick Access Toolbar. Both of these methods open a blank workbook without going through the New Workbook wizard.)  

What are Columns, Rows, and Cells?
Columns are the vertical markers in the worksheet and are denoted by the alphabet i.e. A, B, C. The rows are the horizontal markers in the worksheet and are denoted by numbers i.e. 1, 2, 3. Cells are the single box that you get where the column and row intersect i.e. A1, B3, and C2. 
You will often need to know the cell reference. The cell reference is the cell’s name and you can find that by looking at the toolbar. This means that the cell that is selected is named I5

Exercise 4: selecting a cell

1.            We are going to select C28. Look for the C column. 

2.            Look for the Row number 28.

3.            You may use your finger to follow the column C down to where row 28 is. 

4.            Once you have located it, click on it. 

5.            Look at the toolbar; the cell reference box should say C28. If it does not, try again. 

6.            After you have done this, click on a different cell and note the cell reference box.  

Exercise 5: selecting a group of cells A4 to D10

1.            Click on the first cell A4 

2.            Click and Hold the mouse button down. Drag the cursor down to D10. 

3.            Let go of the mouse button. 

4.            If you did it correctly, you should see a Highlighted box around those cells. If not, try again. 

Exercise 6: Basic data entry, fill handle
From the example above, we have numeric (year, numbers) and text (months) entered as data in our worksheet. Let us practice by re-creating the example on our own.  Method 1:

1.            Click on cell A2 to select it. 

2.            Type in 1981 and hit Enter. Notice by hitting Enter, we automatically move down to the next row. (we can also do the same by hitting the down arrow) 

3.            Click on cell B1 to select it. 

4.            Type in January and hit Tab. By hitting Tab (or right arrow), we move to the next column.

We can continue to doing this to enter the data from 1981 to 1992 and so on, but Excel provides us with a tool to complete sequences.Method 2

1.            Click on cell A2 to select it. 

2.            Type in 1981 and hit Enter. 

3.            Type in 1982, and then select both cells A2 and A3.  

4.            Move your mouse cursor over the fill handle (small black box on the bottom right of the active cell) so that the cursor turns into a cross. 

5.            Click and drag the fill handle down to the cell desired. 

Copy, Cut, Paste 
You can Copy, Cut and Paste anything into your worksheet. You can copy from one worksheet to another worksheet in another book. Let’s concentrate on the basics. We are going to copy cells D4 to H9. 

Exercise 7: Cut/Copy and Paste to the same worksheet

1.            Using the same worksheet, select cells A4 to D10. 

2.            Use CTRL-c to copy and CTRL-x to cut the selected cells. 

3.            Click on cell E29. It should be blank 

4.            Use CTRL-v to paste the data. You can also use the toolbar shortcuts for cut/copy/paste as the functionality is the same. 

Exercise 8: Create example 2

1.            Re-create the example worksheet shown below 

2.            In cell A1, type Full Name and hit Tab 

3.            In cell B1, type Address and hit Tab 

4.            In cell C1, type City and hit Tab 

5.            In cell D1, type State and hit Tab 

6.            In cell E1, type Zip Code and hit Tab 

7.            In cell F1, type Phone and hit Return. 

8.            Continue and add a few examples (do not worry about the content).

Insert & delete columns, rows, and cells 
Have you ever entered all of your data and realized that you are missing an entry in the middle of the worksheet? If yes and you did not know how to add columns or rows, it would be really difficult to fix. Well, there is an easier method. 

You can insert columns, rows, or cells in any spot on your worksheet. 

Exercise 9: Insert columns 
Before you can insert a column or row, you need to know how Excel inserts a column or a row.  

·                     For columns, Excel inserts new column(s) to the left of the selected column(s). 

·                     For rows, Excel inserts new row(s) above the selected row. 

1.            We want to insert a new column for Last Name between Full Name and Address.

2.            Since Excel inserts to the left, you need to click on the column letter B. Column B should be highlighted. 

3.            Point (do not left click) the arrow at the B column and then right click. 

4.            A pop-up menu should appear. In the menu you should see Insert. 

5.            Click on the word Insert. Excel will insert a blank column between Full Name and Address.

6.            Click on B1 and type in Last Name.

Exercise 10: Insert rows

1.            We want to insert a new row for Anne Frank’s contact information between John Doe and Fred Johnson.

2.            Since Excel inserts rows above the selection, you need to click on row number 3. Row 3 should be highlighted.

3.            Right click while pointing at the number 3.

4.            A pop-up menu should appear. In the menu you should see Insert. 

5.            Click on the word Insert. Excel will insert a blank row between John Doe and Fred Johnson.

6.            Click on A3 and type in Anne Frank’s contact information 

Exercise 12: Delete columns and rows

We all make mistakes. It is very easy to remove a column or rows.

1.            Select the column or row that you want to delete.  

2.            Point at the highlighted column name or row name and right click. 

3.            A pop-up menu should appear  

4.            Select Delete 

5.            The column or row should be gone.  

Exercise 13: Insert and delete cells
The insertion or deletion of columns and rows are easy to keep track of. However, when you insert or delete a cell or a group of cells, you need to be mindful of where these cells are. The reason is that the inserted or deleted cells will shift information in the direction of the insertion or deletion. 

1.            Select cells B3 to C4. (Click on B3 and hold the left mouse button. Drag the cursor to point at C4.) 

2.            Right click while pointing anywhere in the highlighted area. 

3.            A pop-up menu should appear and select insert or delete. In the example, I selected the Insert function 

4.            The next window will ask you in which direction would you like to shift the cells. If you selected delete, you will see the same options.

·                                             Shift cells right will shift the cells in columns to the right by the number of columns in the highlighted selection. For example, if you select B3 to C4, we have 2 columns so the Shift cells right will move the selection 2 columns to the right or if it is a deletion, 2 columns to the left.

·                                             Shift cells down will shift the cells in rows downward by the number of rows in the highlighted selection. For example, if you select B3 to C4 we have 2 rows so the Shift cells down will move the selection 2 rows downward or if it is a deletion, 2 rows upward. 

·                                             Entire row and entire column is like using the insert a row or column discussed earlier. 

Inserting & deleting new worksheets
There will be many times when you need to add a whole worksheet rather than columns or rows. 

Exercise 14: Insert a worksheet

1.            Right Click on the tabs where the name of the worksheets are. 

2.            Click on Insert.

3.            Select what kind of insert you want from the window.

4.            To move the worksheet, click and hold the left mouse button on the worksheet tab. Drag the tab to where you want it to be placed. Note: you can only move the worksheet tabs to the left or right. 

Exercise 15: Delete a worksheet

1.            Click on the tab of the worksheet that you want to delete. 

2.            Right Click on the same tab of the worksheet 

3.            Click on Delete

4.            A warning window will appear to ask you if you are sure about the deletion. 

5.            If you select OK, the worksheet will be removed and if you select Cancel, the worksheet will not be removed.  

Copying worksheets
You may want to copy the whole worksheet rather than selecting the cells in the old worksheet to copy to a new one. Besides being easier to do, there might be some links in the cells that do not transfer well. 

Exercise 16: Move or copy a worksheet

1.            Click on the tab of the worksheet that you want to Move or Copy.

2.            Right Click on the same tab of the worksheet 

3.            Click on Move or Copy 

4.            Select where you want the worksheet to go

5.            If you want to COPY, you must click in the Create a copy box; otherwise, Excel will move the worksheet. 

6.            Click on OK 

Printing 
Printing your worksheets out to a printer would be a nice thing to know how to do. If the information that you have on the worksheet fits on an 8.5”x11” paper, you have no trouble printing your worksheet out. The problem arises when you have a larger worksheet. You may have noticed that your worksheet has dashed lines running down and across it; this is your print area. 

Exercise 17: Setting the print area

1.            On your worksheet, click on the Page Layout tab. 

2.            Click and drag to select the range of cells that you would like to print. 

3.            Under Page Layout, click on Print Area, then Set Print Area. 

Exercise 18: How to print with Print Preview

1.            You may click on the paper with the magnifying glass icon or click on the Office Button and click on Print, then Print Preview. 

2.            The menu at the top is important because there are many print functions that you can use for your worksheet. Here are some examples, turn on the gridlines, shrink to fit one page, print headers or footers. 

3.            Click on Print. You will be at the Print menu. 

4.            Click Page Setup. You will see a control panel that will allow you to make some cosmetic changes to the way the worksheet prints. 

·                                             a) Change the printing orientation - Portrait or Landscape 

·                                             b) Scale the worksheet - shrink to fit a single sheet or expand to fit a number of sheets of paper 

·                                             c) Change the paper size

·                                              d) Change the print quality - 600dpi or 300 dpi 

·                                             e) Change the first page number - Default is auto 

·                                             f) Change the margins for the print job 

·                                             a) Change the position of the header and footer 

·                                             b) Center the worksheet on the page - horizontally and/or vertically Page Tab Margins Tab                 

·                                             a) Add a Header - select a pre-made one or customize it 

·                                             b) Add a Footer - select a pre-made one or customize it 

·                                             a) Change the Print area - you can select to print only section 

·                                             b) Repeat titles at the top of the page or on the left hand side of the worksheet 

·                                             c) Turn on the gridlines 

·                                             d) Print only in Black and White (no grayscale) 

·                                             e) Print in draft quality (it uses less toner but it is really ugly and graphics will not be printed out) 

·                                             f) Turn on the Row and column headings (this is the numbers and alphabets for cell reference) 

·                                             g) Change the way Excel prints out the pages - print left to right and then down or print down and then left to right.