Training at Georgetown: Handouts--Excel 2010





	Training at Georgetown: Handouts--Excel 2010 Defining The New Interface You may notice that none of the items listed in the Ribbon have underlined shortcut keys. Unlike previous versions, 2007 applications allow you to access ALL of the items using Access Keys. To activate them press that Alt key and then press a corresponding number or letter to get another set of key options. In this example, I pressed 'F' and the Key Tips for the File Menu were displayed. Preparation for Excel Before beginning, let's change a few display options. Start by moving the Quick Access Toolbar below the ribbon. Not only does it bring your frequently used commands closer, it allows more space to display those options you use often. By Default, your customizable Quick Access Toolbar contains Save, Undo, and Redo. Note: It is recommended that you save often. You can now undo changes even after you have saved your documentation. This is a new feature of the Office 2007 system. When clicking on the options for the Quick Access Toolbar you are presented with a list of frequently used commands. To access all commands, click on "More Commands." Excel provides many different ways to access commands. You can use the Ribbon to select the action wanted, use the Quick Access Toolbar, use Key Tips, or an Office 2003 shortcut key combination. Here is an example of the Copy command I could have clicked on the Quick Access Toolbar icon added earlier, or clicked on "Copy" from the Home Ribbon. I can also use the Ctrl+C shortcut in order to copy text as in previous version of office. For a list of shortcut and other command keys in Excel, use the improved online Help and search for "Keyboard Shortcuts." Workbook / Worksheet When you open Excel, you have entered into a new workbook. Each workbook consists of 3 worksheets ( that is the default number). Additional worksheets can be added. You will also notice on the far right side of your screen there is a scroll bar. Additionally, at the bottom of your screen is a horizontal bar that will scroll left and right. If the scroll bars are not there, you can turn them on by going to File - > Excel Options - > click on the Advanced tab - > and check the boxes for the vertical and horizontal scroll bars and click on OK to close the box. The Formula Bar is the bar bellow the ribbon. It names the range of currently cells and can be used to edit cell contents. Below is an example of the Formula Bar. The Name Box gives the location of your curser, cell A1., and the Formula Bar shows what is written within that cell. The red X and green checkmark allows you to cancel and enter information in that cell that needs to be changed. Clicking into another cell will show the new cell name in the Name Box. When making changes to the content of the cell, you can (a) click in the formula bar, (b) press F2 and your cell will have a blinking cursor within, allowing you to edit, or (c) double - click on the cell. Here is an example of the new formula auto complete in Excel 2007. When you begin to enter a formula by using " = " , the text you enter is compared to the list of formulas and suggestions that are made. Note also the "Tips" that briefly describe the formulas function. Sometimes when you enter information into a cell, your column will not be large enough to display your data. Simply move your cursor between the cells at the top (gray section ) until your cursor becomes a double pointing arrow. Double - click your left mouse button and the cell will automatically open to the size necessary to view the information within. Excel 2007 may automatically do this for numerical values. You no longer get"#####". AutoFill Features There are certain lists that Microsoft Excel has built in. We entered Monday into cell A1. Once you enter this information within your cell, click on the small black box in the lower right - hand corner of your cell. By doing this it initiates AutoFill. The cursor changes from a thick white cross to a thin black cross. Click and drag from the corner over a few more cells. The days should automatically fill. You will see the progress of the drag below the cursor. Here are some additional built in AutoFill lists. If you were to enter a date (1/1/08) and click and drag from the box in the lower right - hand corner, the date would increase with each newly selected cell by 1 day. If you enter a number in a cell and an additional number in an adjacent cell, highlight the two cells and drag the area, Excel will create a pattern from the numbers you inserted (if you enter 5 in one cell, 10 in the next, the cells will automatically fill with a drag of the lower corner box by 5). It knows what increments to use, from the two cells you highlighted before the drag. You can also add your own Auto Fill items. Go to File > Excel Options> Advanced tab. From there, scroll down to and click on "Edit Custom Lists". You can add your own items. If you have items select, you can click on Import or type the list and then click on "Add". Copy and Paste in Excel There are 4 different way to work in Excel with copy and paste. Go to the Home Ribbon and click on copy and paste. To get Paste Special, click on the option arrow below the button. Right click on the cell with your mouse to copy, then paste in the next cell. Use the Quick Access Toolbar if you have added the buttons. Autofill the amount continuously across (or down). Inserting and Deleting a Column / Row or Cell To insert a column or row, from the Home Ribbon, click on "Insert". It is very important to pay attention to the position of your cursor before you request the insertion. Columns will appear to the left of the cell. Rows will appear above the cell you have chosen. You can also click on the column/row header to highlight the entire column /row. Right click on the area and select Insert. If you have columns or rows copied, you no longer need to insert the blank rows ahead of time! Just click on insert and they will be done. One way to delete a row or column, is to highlight the column letter or row number, right click and select "Delete" from the Home Ribbon. However, if you choose to remove a single cell, Excel will ask you how you want to adjust the cells. Decide on the correct option for the cell removal and the chart cells will adjust accordingly. If you have calculations in your table, this will be affected. Be Careful to Maintain Data Integrity! If you have rows of data, be sure to delete an entire row and not a few cells. Text to Column Split If you prepare or paste data into Excel with more than one word in a cell (in this example, we will use first and last names), instead of re-typing in a new column, highlight the range of cells, go to the Data tab in the ribbon and then Text to Columns -> Next-> Choose Delimiter-> Next-> Finish. Your cells will split from one to two. We use ''(space) as the delimiter in this case. Before clicking "Finish" verify that the data in the "Preview" pane is correctly split. Note: You cannot do the same steps to put cells back together. To put cells together, you need to use the Concatenate formula. Concatenating Cells (Combining Cells) Concatenate joins several text strings into one text string, In the example above, you could bring the text back together in a single cell. To do this, you would use the following formula: = Concatenate (A1," ",B1) The space between the quotes is needed to put the space between the words being put together. The text items can be text strings, numbers, or single - cell references. Once you have the first formula done correctly, you can drag and drop the formula using Auto Fill for the remainder of the cells that need to be joined. Selecting Cells Contiguous Cells When creating a formula most of the time you will be selecting a range of cells. If you select a number of cells in a row, up and down, or left to right, this is called a contiguous range. You would click in the beginning cell, then click and hold down the left mouse button and drag the mouse over desired cells. When using Auto Fill you are basically doing this. The black box in the picture above is an example of this. Non - Contiguous Cells Non- contiguous cells are cells that are not touching. To highlight different cells in the worksheet, click on the first cell, hold down the Ctrl key, and then click on the other cells while still holding the Ctrl key. A1, B3 C2, and D5 below. Formatting Cells Here are a few ways to format cells. You can: (1) use the options on the Home ribbon, (2) launch the classic office dialog boxes from the Home Ribbon, (3) or use the new Mini Formatting Toolbar by right - clicking on a selection. New in Office 2007 (introduced in Office 2004 for Macintosh), you can actually preview the formatting options before you apply them. You do this by moving your mouse pointer over an option,but not clicking on it. Try it: Highlight some text and then on the Home Ribbon click on the arrow next to font and point your cursor to another font to see the "Live Preview". If you want to add a heading to a section of your spreadsheet and want to have it centered across the columns, you use what is called "Merge and Center". This is found on the Home Ribbon under Alignment. After selecting your range (which would include your title and headers), you can format the cells. Right click on the area selected then click on Format cells.. At this point you are able to click on the tab that says "Border". You can decide where and what type of border line to use for your spreadsheet. I chose to put a boarder around the outside and inside of the cells that I selected. With the area selected, you can choose a color to fill the range. To change your numbers to currency, select the range and click ont he $ icon on the Home Ribbon from the Number section. This will put your data in the "Accounting" format. I will explain the difference between Accounting and Currency, a new option in Excel 2007 now. Sorting Data If you are sorting information by row or column, then there will be on problem. But, if you have a table created and decide that when it is done, you want the rows in order, you need to highlight the entire table for it to work. ( If you have a title row that is merged and centered, do not include this). It will sort your table in ascending / descending order using the information in the first column. If you do not highlight the entire table, the row will move, but the data will not. I want to sort the expense categories in alphabetical order. To do this I need to sort by the first column which is unlabeled. Please note that, unlike in previous versions, Excel 2010 will not provide you with the column label (i.e., Column A) if you don't have a heading for that column in your table. Formulas The common formulas in Excel are addition, subtraction, multiplication, and division. The symbols used in these calculations are as follows: - subtraction, + addition, * multiplication, and / division. There are different ways to create your formulas in Excel : 1. Write the formula out yourself by starting the cell with an ' = ' 2. Click on Fx in the formula bar, choose the desired formula, and follow the steps to get the desired results 3. Select the sigma icon, AutoSum, on the Home ribbon. Make sure you have the correct calculation selected in the drop down box on the side. Note that in Excel 2007, you have addition auto calculation options in addition to sum. Let's sum the total amount of money we spent each day : Highlighted cell B7( Remember, these are coordinates Column B and Row 7) Clicked on AutoSum, Excel will detect that you want to sum the value adjacent to, in this case above, the highlighted cell. Notice that the formula that was generated "= SUM(B3:B6)." This means, sum the value B3 through B6. The":" means though. Quick Calculations (with no formulas) There will be times, when you need to get a quick calculation for something, but not want to write out the formula. This can be done in Excel. Highlight the cells in question. Right click anywhere on the status bar (the bar at the very bottom of the Excel program window) and you get the box shown below. Choose the features you need and then highlight the cells you need a calculation. If you look at the status bar it will give you the calculation for what you have highlighted. As shown in the example, it will give me the sum of selected cells. This can also help to check to make sure your formula is calculating correctly. Notice that in Excel 2007, you can have more than one displayed at a time. If the calculations are not relevant to your data selection, they are not displayed. AutoFormat Instead of formatting the table yourself, Excel gives you the option to choose a template. Start by highlighting the entire data table, go to Home Ribbon - > Format as Table. Choose from the different templates offered. Once you have chosen your template, you can still change the formatting to your liking. Clearing Vs. Deleting When you want to keep the function, but remove information from a cell with a function, it is important to Clear the cell by right - clicking on the selection and left - clicking on Clear. Deleting a cell removes all removes all formatting and functions. Sorting To alphabetize a spreadsheet, you must remember to highlight the entire table. If you do not highlight all of the data, Excel will only move what is selected. If you use the alpha buttons on the Standard Menu Bar, it will only order by what is in the first column. Excel also allows you to put data in order by what is in a specific row or column. If you need to put a column in order and it's located in the middle of the chart , you need to use the sort key which is located in the Data Ribbon under the category Sort & Filter . Tip: Check the results of your sort immediately. If you are not pleased with the results that are given, use the Undo command to restore the previous order. Creating a Chart With Microsoft Excel 2007, you can create sophisticated charts from your worksheet data. You can chose from a wide range of standards business and technical chart type, each of which has different graphical representation. There are a few basic steps that you must follow: Select your range. You should include the headers for your information as well as the row titles. Do Not include totals or averages. Click insert tab , select chart type on your toolbar - - > Column To move you chart, select chart and drag to desired location. To move the chart to a separate worksheet, right click on the chart and select Move Chart. A dialogue box will appear. Select new sheet. After you create a chart, you can easily add new elements to it. For example, you can add chart titles to add more information to the chart, or change how chart elements are laid out. Select the chart and the layout menu will appear. Excel will give you several layout options and styles. Tip: To quickly format any chart element, you can begin by simply double clicking on the chart or right click on the element you wish to change. A right click will give you a menu with several options. For example, Chart options and Plotting area. Pivot Table A pivot table summarizes information from particular fields, list or database. When you create a pivot table by using Excel Pivot Table Report command the pivot table dialogue box appears and asks you to specify which fields you're interested in, how you want the table organized, and what kind of calculations you want the table to perform. After you have built the table, you can rearrange it to view your data from alternative perspectives. For this section, you will need to download a data file. The instructor will direct you to the download site. You're going to use all the data in the worksheet. Select all the data ranges. Click the insert tab. In the Tables group, click the arrow on Pivot Table, and on the menu, click Pivot Table. (The Create Pivot Table dialog box opens.) If you prefer to build a Pivot Table report by using the drag - and - drop method, as you could in previous versions of Excel, there is still a way to do that. Select the desired field on the Pivot Table field list and drag it over to the Pivot Table on the right. The other option is to drag the fields with the Pivot Table Field list dialogue box. There are four boxes at the bottom of the Pivot Table Field list, The boxes are called Report Filter , Row Labels, Column Labels, and Values. You can drag fields to this area. Now you'll create your report. Begin with this question in mind: How much has the sales rep (Salesperson) sold? To answer, you question you need to add the sales rep and the order amount fields to the report. In the Pivot Table Field List, select the box next to Salesperson. The field is added to the report. Next, in the Pivot Table Field List, click the check box next to the order amount. The field is added in the report. now you can see how much each sales - rep has sold. Tip: If you click in the worksheet outside of the Pivot Table report, you will not be able to view the Pivot Table Field List. To get it back, click anywhere inside the report; for example, click on cell A3. If the field did not appear, it means it has been turned off. At the top of the window, on the Options tab, in the Show / Hide group, click on field List to turn the field list on. Course Completed You have completed Excel 2010 Please visit http://uis.georgetown.edu/training for additional class information and training handouts. If you have any questions, please contact the appropriate Help Desk.