MS excel


 

INTRODUCTION


Excel is the spreadsheet program created by Microsoft.  Although you can use any spreadsheet program for analyzing data, the instructions given here are specific for Excel and you must use Excel for the three Excel quizzes.  NOTE: Microsoft also makes a less powerful spreadsheet program as part of Microsoft Works or some similar title.  Some of the features that we will use in these exercises are not found in MS Works, so you will not be able to complete all the exercises using MS Works.

Excel is, in its most basic form, a very fancy calculator.  The information given in this quick tutorial is meant to give a working knowledge of how to use Excel.  There are usually several different ways to perform the same function in Excel, this tutorial will usually just give one way.  If you need more information on how to use Excel, there are many web sites dedicated to using Excel, a simple ‘google’ search will find many of them.  In addition, accessing the HELP menu from within the program can also be useful!

KKKKK
PARTS OF WORKBOOK


Nearly every part of the spreadsheet has its own term and it is quite useful to know your spreadsheet vocabulary to make using Excel that much easier. There are three basic parts of an Excel Spreadsheet:

  1. Column – The vertical segments that you see on the spreadsheet are called columns.
  2. Row – The horizontal segments are referred to as rows.
  3. Cell – Each box that is created from a row and column intersecting is referred to as a cell.

There are all very important terms, so we will now be going into greater depth on exactly what they are.

 

COMPONENTS OF EXCEL:


Worksheet Components

The Address Bar – This displays the address of the currently selected Cell. The Letter represents the column index, and the number represents the Row Number. In the example above, the cell in Column D, Row Number 8 is selected. If more than one cell is selected, then the address of the upper-left cell is displayed. There is more to it than this (there is an alternate addressing notation for example, called R1C1, that can be used by adjusting some optional settings. We aren’t going to worry about that right now), but in general, the address bar will let you know which cell is currently selected within your Worksheet.

The Formula Bar – When you select a cell, the value stored in the cell is displayed in the formula bar. The contents of a given cell might be discrete data, or the cell might contain a formula. In either case, the actual cell contents are displayed here, and can be edited by clicking in the Formula Bar and typing. Note that you can also edit cell contents directly in the cell either by selecting and typing (which will overtype anything already in there) OR by selecting the cell and then double-clicking, which will place it in “edit mode”. Then you can selectively edit the item in the cell.

Page Area Boundary – The boundary which defines what will appear on a printed page. This does not appear until you open the “Print” dialog (from the File Menu Tab – remember?), because Excel cannot determine this until you have selected a printer (different printers have different minimum print areas and such). If the contents of the portion to the left of the dotted line extend past it, they will be printed on another page. There is a similar boundary defining the bottom of each page (not visible here).

We will examine setting up to print in more detail later. For now, realize that you should pay attention to this dotted line before you print. You can also use the “Print Preview” option from the File/Print to see more exactly what your document will look like in print.

Worksheet Scrollbars – Self-explanatory, but important nonetheless. As you become more proficient working with spreadsheets, you will find that they can sometimes become . . . wide. We all use the mouse wheel for vertical scrolling, and that horizontal scroll bar does not, by default, traverse the entire lower edge of the window, so it is easily lost or forgotten about by new users.

Zoom Control – Allows zooming in or out on the active Worksheet within the Workbook window. Often, when the becomes wide, tall, or both, it can be handy to zoom out for a more macroscopic view, then zoom back in again. Sometimes users have their own preferences about screen magnification. In any case, this control zooms in and out, if you are more comfortable using the GUI controls. Zooming can also be accomplished by holding down the Ctrl key (Control+Shift in Mac) and using the mouse wheel to zoom.

 

CREATING,SAVING AND OPENING WORKBOOK


 

Creating a workbook

A blank workbook is displayed when Microsoft Excel is first opened. You can type information or design a layout directly in this blank workbook.

To create an Excel workbook:

  • Choose File New from the menu bar.
    File New Menu Selection
  • The New Workbook task pane opens on the right side of the screen.Select Blank Workbook
  • Choose Blank Workbook under the New category heading.
  • A blank workbook opens in the Excel window. The New Workbook task pane is closed.

Saving a workbook

Every workbook created in Excel must be saved and assigned a name to distinguish it from other workbooks. The first time you save a workbook, Excel will prompt you to assign a name through the Save As operation. Once assigned a name, any additional changes made to the text, numbers, or formulas must be saved using the Saveoperation.

To save a new workbook:

  • Choose File Save As from the menu bar.File and Save As Menu Selection
  • The Save As dialog box appears.
  • Click the Save In: drop-down menu, and locate where the file will be saved.
  •  Local Disk (C:) to save the file to your computer.
  • Type a name for your file in the File Name: box.
  • Click the Save button.The Save As Dialog Box

To save changes made to an existing workbook:

  • Choose File Save from the menu bar, orClick the Save button Save button on the Standard toolbar.

If you’re saving the file for the first time and you do not choose a file name, Microsoft Excel will assign a file name for you.

It’s a good idea to save frequently when working in a spreadsheet. Losing information is never fun! You can quickly save your spreadsheet by using the quick-key combination Ctrl + S.

Opening a workbook

You can open any workbook that has previously been saved and given a name.

To open an existing Excel 2003 workbook:

  • Choose File Open from the menu bar.File and Open Menu Selection
  • The Open dialog box opens.The Open Dialog Box
  • In the Look in list, click the drive, folder, or Internet location that contains the file you want to open.
  • In the folder list, open the folder that contains the file. Once the file is displayed, click the file you want to open.
  • Click the Open button.

Formatting text and numbers

The ability to apply specific formatting for text and numbers is one of the most powerful tools in Google Sheets. Instead of displaying all cell content in exactly the same way, you can use formatting to change the appearance of dates, times, decimals, percentages (%), currency ($), and much more.

To applying number formatting:

  1. Select the cell or cells you want to modify. In our example, we’ll change the date formatting of cell A2.
  2. Locate and select the Number Formatting button Format numbers button from the shortcut toolbar.
    Screenshot of Google Spreadsheets
  3. A drop-down menu will appear with various number formatting options.
  4. Select the desired formatting. In our example, we’ll select a different type of date formatting.
    Screenshot of Google Spreadsheets
  5. The cell will change to the new formatting style.
    Screenshot of Google Spreadsheets

Click the buttons in the interactive below to more learn about text and number formatting.

To change the font:

By default, the font of each new workbook is set to Calibri. However, Excel provides many other fonts you can use to customize your cell text. In the example below, we’ll format our title cell to help distinguish it from the rest of the worksheet.

  1. Select the cell(s) you want to modify.
    Screenshot of Excel 2013
  2. Click the drop-down arrow next to the Font command on the Home tab. The Font drop-down menu will appear.
  3. Select the desired font. A live preview of the new font will appear as you hover the mouse over different options. In our example, we’ll choose Georgia.
    Screenshot of Excel 2013
  4. The text will change to the selected font.
    Screenshot of Excel 2013

When creating a workbook in the workplace, you’ll want to select a font that is easy to read. Along with Calibri, standard reading fonts include Cambria, Times New Roman, and Arial.

To change the font size:

  1. Select the cell(s) you want to modify.
    Screenshot of Excel 2013
  2. Click the drop-down arrow next to the Font Size command on the Home tab. The Font Size drop-down menu will appear.
  3. Select the desired font size. A live preview of the new font size will appear as you hover the mouse over different options. In our example, we will choose 16 to make the text larger.
    Screenshot of Excel 2013
  4. The text will change to the selected font size.
    Screenshot of Excel 2013

You can also use the Increase FontSize and Decrease FontSize commands or enter a custom font sizeusing your keyboard.

Screenshot of Excel 2013

To change the font color:

  1. Select the cell(s) you want to modify.
    Screenshot of Excel 2013
  2. Click the drop-down arrow next to the Font Color command on the Home tab. The Color menu will appear.
  3. Select the desired fontcolor. A live preview of the new font color will appear as you hover the mouse over different options. In our example, we’ll choose Green.
    Screenshot of Excel 2013
  4. The text will change to the selected font color.
    Screenshot of Excel 2013

Select More Colors at the bottom of the menu to access additional color options.

Screenshot of Excel 2013

To use the Bold, Italic, and Underline commands:

  1. Select the cell(s) you want to modify.
    Screenshot of Excel 2013
  2. Click the Bold (B), Italic (I), or Underline (U) command on the Home tab. In our example, we’ll make the selected cells bold.
    Screenshot of Excel 2013
  3. The selected style will be applied to the text.
    Screenshot of Excel 2013

You can also press Ctrl+B on your keyboard to make selected text bold, Ctrl+I to apply italics, and Ctrl+U to apply an underline.

                                                                             KKKKK

Create a chart


 

You can create a chart in Excel, Word, and PowerPoint. However, the chart data is entered and saved in an Excel sheet. If you insert a chart in Word or PowerPoint, a new sheet is opened in Excel. When you save a Word document or PowerPoint presentation that contains a chart, the chart’s underlying Excel data is automatically saved within the Word document or PowerPoint presentation.

  1. On the View menu, click Print Layout.
  2. On the Charts tab, under Insert Chart, click a chart type, and then click the one that you want to add.Charts tab, Insert Chart groupWhen you insert a chart into Word or PowerPoint, an Excel sheet opens that contains a table of sample data.
  3. In Excel, replace the sample data with the data that you want to plot in the chart. If you already have your data in another table, you can copy the data from that table and then paste it over the sample data. See the following table for guidelines on how to arrange the data to fit your chart type.

    FUNCTIONS IN EXCEL


     

    Each function has a specific order, called syntax, which must be strictly followed for the function to work correctly.

    Syntax order:

    1. All functions begin with the = sign.
    2. After the = sign, define the function name (e.g., Sum).
    3. Then there will be an argument. An argument is the cell range or cell references that are enclosed by parentheses. If there is more than one argument, separate each by a comma.

    An example of a function with one argument that adds a range of cells, A3 through A9:

    Function With One Argument

    An example of a function with more than one argument that calculates the sum of two cell ranges:

    Function With Two Arguments

    Excel literally has hundreds of different functions to assist with your calculations. Building formulas can be difficult and time consuming. Excel’s functions can save you a lot of time and headaches.

    Page 3

    Excel’s different functions

    There are many different functions in Excel 2007. Some of the more common functions include:

    Statistical functions:

    • SUM: Adds a range of cells together
    • AVERAGE: Calculates the average of a range of cells
    • COUNT: Counts the number of chosen data in a range of cells
    • MAX: Identifies the largest number in a range of cells
    • MIN: Identifies the smallest number in a range of cells

    Financial functions:

    • Interest rates
    • Loan payments
    • Depreciation amounts

    Date and time functions:

    • DATE: Converts a serial number to a day of the month
    • Day of Week
    • DAYS360: Calculates the number of days between two dates based on a 360-day year
    • TIME: Returns the serial number of a particular time
    • HOUR: Converts a serial number to an hour
    • MINUTE: Converts a serial number to a minute
    • TODAY: Returns the serial number of today’s date
    • MONTH: Converts a serial number to a month
    • YEAR: Converts a serial number to a year

    You don’t have to memorize the functions, but you should have an idea of what each can do for you.

    Page 4

    To calculate the sum of a range of data using AutoSum:

    • Select the Formulas tab.
    • Locate the Function Library group. From here, you can access all available functions.
    • Select the cell where you want the function to appear. In this example, select G42.
    • Select the drop-down arrow next to the AutoSum command.
    • Select Sum. A formula will appear in the selected cell, G42.
      • This formula, =SUM(G2:G41), is called a function. The AutoSum command automatically selects the range of cells from G2 to G41, based on where you inserted the function. You can alter the cell range if necessary.
    Calculate Sum with AutoSum
    • Press the Enter key or Enter button on the formula bar. The total will appear.

    Excel will not always tell you if your formula contains an error, so it’s up to you to check all of your formulas. To learn how to do this, read the Double-Check Your Formulas lesson from our Excel Formulas tutorial.

    To edit a function:

    • Select the cell where the function is defined.
    • Insert the cursor in the formula bar.
    • Edit the range by deleting and changing necessary cell numbers.
    Edit Range
    • Click the Enter icon.

    Page 5

    To calculate the sum of two arguments:

    • Select the cell where you want the function to appear—in this example, G44.
    • Click the Insert Function command on the Formulas tab. A dialog box appears.
    • SUM is selected by default.
    Insert Function Dialog Box

     

    • Click OK, and the Function Arguments dialog box appears so you can enter the range of cells for the function.
    • Insert the cursor in the Number 1 field.
    • In the spreadsheet, select the first range of cells—in this example, G21 through G26. The argument appears in the Number 1 field.
      • To select the cells, left-click cell G21 and drag the cursor to G26, then release the mouse button.
    • Insert the cursor in the Number 2 field.
    Function Arguments

     

    • In the spreadsheet, select the second range of cells—in this example, G40 through G41. The argument appears in the Number 2 field.
    • Notice that both arguments appear in the function in cell G44 and the formula bar when G44 is selected.
    Function Arguments Second Function

     

    • Click OK in the dialog box, and the sum of the two ranges is calculated.

    To calculate the average of a range of data:

    • Select the cell where you want the function to appear.
    • Click the drop-down arrow next to the AutoSum command.
    • Select Average.
    • Click on the first cell (in this example, C8) to be included in the formula.
    • Left-click and drag the mouse to define a cell range (C8 through cell C20, in this example).
    • Click the Enter icon to calculate the average.

    Page 6

    Accessing Excel 2007 functions

    To access other functions in Excel:

    • Using the point-click-drag method, select a cell range to be included in the formula.
    • On the Formulas tab, click the drop-down part of the AutoSum button.
    • If you don’t see the function you want to use (Sum, Average, Count, Max, Min), display additional functions by selectingMore Functions.
    • The Insert Function dialog box opens.
    • There are three ways to locate a function in the Insert Function dialog box:
      • You can type a question in the Search for a function box and click GO.
      • You can scroll through the alphabetical list of functions in the Select a function field.
      • You can select a function category in the Select a category drop-down list and review the corresponding function names in the Select a function field.

    • Select the function you want to use, then click the OK button.

    PROTECTING WORKSHEETS AND CELLS


    You can lock cells if you want to protect cells from being edited. In this example, we will lock cell A2.

    Lock Cells Example

    Before you start: by default, all cells are locked. However, locking cells has no effect until you protect the worksheet. So when you protect a worksheet, all your cells (=worksheet) will be locked. As a result, if you want to lock a cell, you have to unlock all cells first, lock a cell, and then protect the sheet.

    1. Select all cells.

    Select All Cells

    2. Right click, and then click Format Cells.

    3. On the Protection tab, uncheck the Locked check box and click OK.

    Unlock Cells

    4. Right click cell A2, and then click Format Cells.

    5. On the Protection tab, check the Locked check box and click OK.

    Lock Cell

    Note: if you also check the Hidden check box, users cannot see the formula in the formula bar when they select cell A2.

    6. Protect the sheet.

    Cell A2 is locked now. To edit cell A2, you have to unprotect the sheet. The password for the downloadableExcel file is “easy”. You can still edit all other cells.

    SHORTCUTS USED IN EXCEL


    Shortcut Description
    F2 Edit the selected cell.
    F3 After a name has been created, F3 will paste names.
    F4 Repeat last action. For example, if you changed the color of text in another cell, pressing F4 will change the text in cell to the same color.
    F5 Go to a specific cell. For example, C6.
    F7 Spell check selected text or document.
    F11 Create chart from selected data.
    Ctrl + Shift + ; Enter the current time.
    Ctrl + ; Enter the current date.
    Alt + Shift + F1 Insert New Worksheet.
    Alt + Enter While typing text in a cell, pressing Alt + Enter will move to the next line, allowing for multiple lines of text in one cell.
    Shift + F3 Open the Excel formula window.
    Shift + F5 Bring up search box.
    Ctrl + 1 Open the Format Cells window.
    Ctrl + A Select all contents of the worksheet.
    Ctrl + B Bold highlighted selection.
    Ctrl + I Italic highlighted selection.
    Ctrl + K Insert link.
    Ctrl + S Save the open worksheet.
    Ctrl + U Underline highlighted selection.
    Ctrl + 1 Change the format of selected cells.
    Ctrl + 5 Strike through highlighted selection.
    Ctrl + P Bring up the print dialog box to begin the printing process.
    Ctrl + Z Undo last action.
    Ctrl + F3 Open Excel Name Manager.
    Ctrl + F9 minimize current window.
    Ctrl + F10 maximize currently selected window.
    Ctrl + F6 Switch between open workbooks or windows.
    Ctrl + Page up Move between work sheets in the same document.
    Ctrl + Page down Move between work sheets in the same document.
    Ctrl + Tab Move between Two or more open Excel files.
    Alt + = Create a formula to sum all of the above cells.
    Ctrl + ‘ Insert the value of the above cell into the cell currently selected.
    Ctrl + Shift + 1 Format number in comma format.
    Ctrl + Shift + 4 Format number in currency format.
    Ctrl + Shift + 3 Format number in date format.
    Ctrl + Shift + 5 Format number in percentage format.
    Ctrl + Shift + 6 Format number in scientific format.
    Ctrl + Shift + 2 Format number in time format.
    Ctrl + Arrow key Move to next section of text.
    Ctrl + Space Select entire column.
    Shift + Space Select entire row.
    Ctrl + – Delete the selected column or row.
    Ctrl + Shift + = Insert a new column or row.
    Ctrl + Home Move to cell A1.
    Ctrl + ~ Switch between showing Excel formulas or their values in cells.

 source: internet and websites