Download Introduction to Microsoft Excel Basics and more Lecture notes Computer science in PDF only on Docsity!
Last Updated July 2015
EXCEL BASICS: MICROSOFT OFFICE 2007
GETTING STARTED PAGE 02
Prerequisites What You Will Learn USING MICROSOFT EXCEL PAGE 03 Opening Microsoft Excel Microsoft Excel Features Keyboard Review Pointer Shapes MICROSOFT EXCEL BASICS PAGE 08 Typing in Cells Formatting Cells Inserting Rows and Columns Sorting Data Basic Formulas Cell Reference AutoSum and Excel Equations CLOSING MICROSOFT EXCEL PAGE 1 6 Saving Spreadsheets Printing Spreadsheets Finding More Help Closing the Program
To complete feedback forms, and to view our full schedule, handouts, and
additional tutorials, visit our website:
cws.web.unc.edu
GETTING STARTED
Prerequisites: This is a class for beginning computer users. You are only expected to know how to use the mouse and keyboard, open a program, and turn the computer on and off. You should also be familiar with the Microsoft Windows operating system. Today, we will be going over the basics of using Microsoft Excel. We will be using PC desktop computers running the Windows operating system. Microsoft Excel is part of the suite of programs called “Microsoft Office,” which also includes Word, PowerPoint, and more. Please let the instructor know if you have questions or concerns before the class, or as we go along. You Will Learn How to: Find and open Microsoft Excel in Windows Use Microsoft Excel’s menu and toolbar Review the keyboard functions Understand the different pointer shapes Type in cells Format cells Insert rows and columns Sort your data Basic formulas Cell references Use Autosum Save worksheets Print worksheets Exit the program
The Ribbon Menu System The tabbed Ribbon menu system is how you navigate through Excel and access various Excel commands. If you have used previous versions of Excel, the Ribbon system replaces the traditional menus. At the bottom, left area of the spreadsheet, you will find worksheet tabs. By default, three worksheet tabs appear each time you create a new workbook. On the bottom, right area of the spreadsheet you will find page view commands, the zoom tool, and the horizontal scrolling bar. The Microsoft Office Button Above the Ribbon in the upper-left corner is the Microsoft Office Button. When you left-click the button, a menu appears. From this menu, you can create a new spreadsheet, open existing files, save files in a variety of ways, and print. You can also add security features, send, publish, and close files.
Quick Access Toolbar On the top left-hand side of the Title Bar, you will see several little icons above the File menu. These let you perform common tasks, such as saving and undoing, without having to find them in a menu. We’ll go over the meanings of the icons a little later. The Home Tab The most commonly used commands in Excel are also the most accessible. Some of these commands available in the Home Tab are: New | Open | Save | Print | Preview AutoSum | Sort Font | Style | Font Size | Font Color | Text Alignment The Home Tab Toolbar offers options that can change the font, size, color, alignment, organization and style of the text in the spreadsheet and individual cells. For example, the “Calibri” indicates the FONT of your text, the “11” indicates the SIZE of your text; etc. We will go over how to use all of these options to format your text in a little while. Each of these options expands into a menu if you left-click on the tiny down-arrow in the bottom right corner of the window. This tab works the exact same way as the MS Word Formatting Toolbar. The main difference is that the format changes will only affect the selected cell or cells, all unselected cells remain in the default setting (“Calibri” font, size “11”). Formula Bar The Formula Bar, also known as the Equation Editor, is generally found below the ribbon menu. The left side denotes which cell is selected (“A1”) and the right side allows you to input
Pointer Shapes As with other Microsoft programs, the pointer often changes its shape as you work in Excel. Each pointer shape indicates a different mode of operation. This table shows the various pointer shapes you may see while working in Excel.
MICROSOFT EXCEL BASICS
Typing in Cells Cells are the small rectangular boxes that make up the spreadsheet. The boxes are the intersection of columns (A, B, C, etc.) and rows (1, 2, 3, etc.). To reference a cell, use the column and the row name. For example, the cell in the first column and first row is called “A1”. All the information entered into an Excel spreadsheet is entered into cells. Click on a cell to begin typing in it. It is that easy! When you are finished typing in the cell, press the Enter key and you will be taken to the next cell down. You can then begin typing in that cell. You can easily navigate around the cells using your arrow keys. Keep in mind that the Formatting toolbar in Microsoft Excel 2007 is exactly the same as the one used for Microsoft Word 2007. The biggest difference between the two programs is that, in Excel, the format is set for each individual cell. So if you change the font and applied the bold option in cell C5, then this format will only be applied to cell C5. All remaining cells will remain in default mode until they have been changed. Sometimes you may only wish to adjust the format of one particular cell. In this case, simply select the cell by clicking the mouse on it and make any necessary adjustments to the font, size, style, and alignment. Those changes will not carry over when you begin typing in a new cell. Other times, you may wish to adjust the text format of a group of cells, entire rows, or entire columns. In Excel, you can choose groups of cells in rectangular units—all the cells you select must form a rectangle of some kind. To select a group of cells, begin by clicking on the cell that would be in the upper-left hand corner of your rectangle. Hold down the Shift key on your keyboard and use the arrows (←, →, ↑, ↓) on the keyboard to expand the selection of cells, or click and drag your mouse. Once the group of cells has been selected, you can make adjustments to the font, size, style, and alignment and they will be applied to all selected cells. PRACTICE: Select cell A1. Type 123 in that cell and press Enter on the keyboard. Select cell C6. Type abc in that cell and press Tab on the keyboard. Pressing Enter, Tab or left-clicking another cell will indicate to Excel that you are done typing in that cell. Left-click in the Formula Bar. You should now see a blinking cursor in the bar. Type hello in the bar and press Enter. As you’re typing in the formula bar, the data appears in the highlighted cell. Now use the Undo button in the quick access toolbar to remove “hello”. Select cell C6. Press the delete button on your keyboard to delete “abc” from that cell.
For any given cell or selected cells, you can also format the way your data is represented within the cell(s). Select a single cell or multiple cells. Again, from the “ Home ” tab of the Ribbon Menu, within the “ Cells ” box, click on “ Format .” Select “ Format Cells .” The format window will now appear, giving you a wide variety of options on how to format your cell. Number – This allows you to choose how to represent the numbers that are entered into a cell (number, currency, time, etc.). Alignment – This determines how the data will be aligned within the cell (left-side, centered, or right-side). Font – Select the type of font to be used within the cells. Border – This option lets you choose what type of border, if any, you would like around the cells or some of the cells. Fill – This allows you to change the background color of the cell. Protection – This option allows you to “lock” cell information so that other users cannot make changes. To select an entire row, click on the Row Number with your mouse—note how the entire row becomes highlighted. All formatting changes will now be applied to the whole row. To select an entire column, click on the Column Number with your mouse— again, the entire column will become highlighted. All formatting changes will be applied to the whole column. Inserting Rows and Columns When you are working on a spreadsheet, you may realize that you left out a row or column of data and need to add it in. To insert a row , click on the row below where you want your new row to be (remember to click on the row number to highlight the entire row). From the “ Home ” tab, within the “ Cells ” box, click “ Insert .” Select “ Insert Sheet Rows .” A new row will
automatically be inserted and the row numbers automatically adjusted. To insert a column , click on the column to the right of where you want your new column to be (remember to click on the column letter to highlight the entire column). From the “ Home ” tab, within the “ Cells ” box, click “ Insert .” Select “ Insert Sheet Columns .” A new column will automatically be inserted and the column letters automatically adjusted. PRACTICE: Enter the data as shown in the screenshot below. Now let’s insert a heading. Insert a row above row 1. In cells A1 and B1 enter Bill and January. Sorting Data Once you have created your spreadsheet and entered in some data, you may want to organize the data in a certain way. This could be alphabetically, numerically, or another way. First, select all the cells that represent the data to be sorted, including the header descriptions by selecting cell A1 and click and dragging down and to the right until all the cells you want to sort are selected. Using the mouse, select Sort & Filter from the Editing panel. Select Custom Sort… The following window should appear: Ensure that the “My data has headers” box is checked.
- Your entire equation should look like this: =181+
- Press the Enter key.
- You will now see the sum appear in the cell – in this case, 557. You can repeat these steps for subtraction, multiplication, or division too. Instead of the plus sign (+), use - for subtraction, * for multiplication, and / for division. Cell References What if you want to be able to add numbers in two different cells, but those numbers might change and you don’t want to have to retype your equation every time they do? In situations like this, you can use cell references within your equation. This tells Excel to use whatever value is in a specific cell to calculate the equation. That way, if the number in one of the cells does change, your equation will automatically recalculate the answer. In the example to the right, we want to add the contents of cells B3 and B4 (181 + 376). We could write our formula the way we did above, but then if we changed the value in cell B3 to 200, our equation wouldn’t reflect the sum of these two cells anymore. We would have to update the equation manually. Instead, we can write our equation using the names of the cells rather than the numbers in them. The name of a cell is its column letter and its row number (A2, C3, etc.). To write a formula using cell references:
- Type the numbers you want to add in two different cells.
- Click on the cell where you want the answer to the equation to appear.
- Begin the equation by typing the = sign.
- Either click on or type in the name of the cell with the first number to be added (in this example, B3).
- Type the + sign.
- Either click on or type in the name of the cell with the second number to be added (in this example, B4). Your equation should look like this: =B 3 +B 4
- Press the Enter key.
- You will now see the answer appear in the cell – in this example, 557. Now, if you changed the value of cell B3 to 200, the answer that appears in the cell where you typed your equation will be 576 (200+376). It automatically recalculates when one of the numbers in a referenced cell changes. Once you have entered your equation, when you click on the cell with that equation it will highlight the cells it is referencing by drawing colored borders around them (see the example above). This helps you see if it is using the cells you want it to use or if you have made a mistake in typing the formula. AutoSum and Excel Equations One of the most powerful features of Excel is its ability to perform basic math functions on data. Excel can add , subtract , multiply , divide , find the average , and perform general counting functions on the numerical data that you enter. To enable this feature, highlight all of the cells in a column, plus one additional empty cell in which to display the result. Select the AutoSum icon from the ribbon menu: If you click directly on the ∑, Excel will automatically add up the numbers you have selected. If you click on the little dropdown arrow next to it (▼), you will get the full choice of mathematical functions. If you double-click on the cell in which the answer appears, you will see an equation that looks something like this (you will also see this equation in the Formula bar): Let’s break down what exactly the equation means: = indicates that you are starting an equation in this cell. SUM tells the function to be performed. In this case, all the cells will be added together. ( ) The parentheses contain the cells that the function will be performed on. D2 This is the first cell to be included in the addition formula. D 10 This is the last cell to be included in the addition formula. : indicates that all cells between the first and the last should be included in the formula.
CLOSING MICROSOFT EXCEL
Saving Spreadsheets When you finish your spreadsheet and want to leave the computer, it is important to save your work, even if you are printing a hard copy. To save your work in Excel, it is essential to know WHAT you are trying to save and WHERE you are trying to save it. Click the Office Button, then hover your mouse over Save As. Select Excel Workbook. You can change the filename that Excel has chosen just by typing a new one in the “File name” box at the bottom of the window that appears. The My Documents folder on your computer’s hard drive is a good place to store your documents. A blank CD or a USB jump drive are great portable storage options and can contain a LOT of data. Excel will automatically save your document with the suffix “.xls”–this is simply a tag that lets Excel know that your work is specific to this program and what version it is in. You do not have to type it–just highlight what is there (default is “Book1”) and write a new file name. You may also chose to save it in an older format so that it can be opened with older versions of Excel. After the first save, you can just click “Save” to preserve your work. However, it is important to note that every following command of SAVE will overwrite your original file, creating the most up-to-date version. If you would like to keep saving different versions of your worksheet, be sure to use the “Save As” function each time you save, using a slightly different name for each version. Printing Spreadsheets To print your Excel document, click on the Office Button, then click “Print” from the menu. From the window that pops up, you can make changes to your print job and release it. As with all commands in Excel, you can make changes along the way. You can change the number of copies you would like to print, change the paper orientation, choose which printer you want to use, and more.
Other useful tools are the Print Preview function found alongside the Print command and the Page Setup function. Print Preview will allow you to look over an exact copy of what will come out of the printer before actually executing the print command. Page Setup will allow you to select the page order in which multiple pages will be printed and to determine if the Gridlines should be printed or not. Finding More Help You can get help with Excel by clicking on the Question Mark symbol in the upper-right hand corner of the main menu bar or by pressing the “F1” button. This will take you to help from Office.com, Microsoft’s help website. There are also many other resources and tutorials available online. You might try a Google search with the words “Excel 2010” and the function you are trying to perform. Ask your instructor for help finding these resources if you have any trouble. Closing the Program Click on the Office Button, then click “Exit.” OR Click on the X in the top right corner of the Excel screen. It’s that easy! If you don’t save before attempting to close the program, Excel will prompt you to save the file. Make sure you save if you don’t want to lose any changes!! NOTE: Images and screen captures may differ from those seen on another system. This work is licensed under a Creative Commons Attribution-NonCommercial 4. International License.
