Exploring Excel: Manipulating Data and Creating Charts, Study Guides, Projects, Research of MS Microsoft Excel skills

Download Exploring Excel: Manipulating Data and Creating Charts and more Study Guides, Projects, Research MS Microsoft Excel skills in PDF only on Docsity! Intro to Excel spreadsheets What are the objectives of this document? The objectives of document are: 1. Familiarize you with what a spreadsheet is, how it works, and what its capabilities are; 2. Using the concepts introduced earlier in the course, apply certain mathematical manipulations to data; 3. Provide you with the tools to make decisions that are more informed and present reports to interested stakeholders in your respective offices. Before we start… Throughout the following pages, we will reference several menu options and how you can get to them. In order to do this, we will use the following convention: when you see the following, ViewZoom, the first word (View) refers to a menu option usually found in the top left, under the title bar. The word that follows (Zoom) is a menu choice found under the first option you made. What is a spreadsheet? A . It has taken the place of the pencil, paper, and calculator. Spreadsheet programs were first developed for accountants but have now been adopted by anyone wanting to prepare a budget, forecast sales data, create profit and loss statements, compare financial alternatives and any other mathematical applications requiring calculations. spreadsheet is the computerized equivalent of a general ledger The electronic spreadsheet is laid out similar to the paper ledger sheet in that it is divided into columns and rows. Any task that can be done on paper can be performed on an electronic spreadsheet faster and more accurately. The problem with manual sheets is that if any error is found within the data, all answers must be erased and recalculated manually. With the computerized spreadsheet, formulas can be written that are automatically updated whenever the data are changed. What can a spreadsheet do? In contrast to a word processor, which manipulates text, a spreadsheet manipulates numerical data and text. Using a spreadsheet, one can create budgets, analyze data, produce financial plans, and perform various other simple and complex numerical applications. By having formulas that automatically recalculate, either built by you, the user, or the built-in math functions, you can play with the numbers to see how the result is affected. Using this “what-if?” analysis, you can see what affect changing a data value or calculation can have on your monitoring program. Spreadsheets can also be used for graphing data points, reporting data analyses, and organizing and storing data. 1 Starting Excel You are encouraged to start using MS Excel as you read through the following materials to familiarize yourself with the topics and procedures. 1. Click the Start button on the Windows taskbar. a. The Start menu opens 2. Point to Programs a. The Programs menu opens 3. Click Microsoft Excel a. Excel opens a new workbook Note: an icon for MS Excel may be located either on the desktop or on the Office toolbar. Figure 1 3. MS Excel 1. The Start button 2. Programs 2 Using “Help” Excel, along with many of the Microsoft applications, has its own online help menu. There are several ways to access help. Either press F1 on the keyboard or choose Help  Microsoft Excel Help from the Menu bar. A window will appear as shown in figure 3. Figure 3 Moving around in Excel When Excel starts, a new worksheet opens. What is currently visible is only a small portion of what is available for you to use. In order to move to areas that you cannot see, you can: • Use the scroll bars • Use the keys described in table 1 Keystroke Result Arrow key Move one cell in the direction of the arrow Ctrl + arrow key Move in the direction of the arrow to the last cell before a blank cell, or to the edge of the worksheet if all cells are blank Page Up Moves up one screen Page Down Moves down one screen Home Moves to the beginning of the row Ctrl + Home Moves to cell A1 Ctrl + End Moves to the last cell containing data (in the bottom right of the worksheet Table 1 5 Data Entry In the following section, you will learn how to enter sample data, edit that sample data, and delete & undelete that data. You should create a sample spreadsheet so you can practice the following procedures. Entering data is as simple as beginning to type. 1. Click once on the cell you want to use for data entry and begin typing 2. The following keys can be used to update the contents of the cell: Enter, Tab, or any of the directional arrows Editing data is simple as well. There are several options for doing this: 1. Highlight the cell, type in a completely new amount (caution: this will overwrite any data already in the cell) 2. Double-click the cell and a flashing insertion point (cursor) appears in the cell 3. Use the formula bar 4. Highlight the cell to edit and press F2 on your keyboard Deletion of data can be relatively straightforward. You can: 1. Select a cell or range of cells (click and hold your mouse or use the shift-click method) and press delete 2. Select a cell or range of cells and Edit Clear then choose from All, Contents, or Formatting from the menu bar 3. To actually remove the cells instead of just clearing the data, select a cell or range of cells and Edit Delete…; you are given the option of shifting the remaining cells a direction or deleting the entire row or column. Undoing an action can save both time and headache. In the toolbar, you will find two arrows. Using these arrows, you can either undo (arrow pointing left) the last action or series of actions you just completed, or Redo (arrow pointing right) an action such as formatting or deleting; you can even Redo an action that was undone. Let Excel enter data for you Excel can help you enter series of numbers, dates or times. For instance, if you want to fill a column with a list of consecutive or patterned dates or numbers, instead of typing dates or numbers in each cell of the column you can use the “Series” command or you can click and drag the “fill handle” on a cell. Both of these methods are described below. You can use a pre-determined series using the series command that you can customize (for instance, date fills can be weekly instead of daily), or you can enter several logical pieces of the series by hand and when selecting cells, include your custom series. Excel will fill the cells with a series based on the cells in the original series selection 6 Using the Series command. Select the cell that contains the first date or number. Use your mouse to drag the selection box down the number of rows that you wish to fill. Go to the Edit menu, and select Fill, then Series. The Series window will appear. For this example, chose “Date” on the Type list and “Day” on the Date Unit list. Click on OK and the selected cells will be filled with consecutive dates. Figure 4 Using the Fill Handle When you select a cell, a small black square appears in one corner of the selection. When you point to the fill handle, the pointer changes to a black cross. Left click with the black cross and hold it down while you drag the selection box over the cells that you want to fill. When you release the mouse button, the boxes will fill automatically. Figure 5 Formatting Once you have created your worksheet, you will want to format it to make it as clear as possible. Formatting is the structure and layout of a worksheet and its individual parts. Using some of the tools available, you can change the alignment, font size and weight, the way numbers display, even add borders and shading to your finished product. Column Width Sometimes the data you enter does not fit the default cell width of 8.43 characters. When this happens, you will see either ##### or see a number expressed in scientific notation (2.34E+08). To fix this, you will have to adjust the cell width. There are two options available to do this: 1. Make sure the highlighted cell is in the column that you want adjusted. Choose Format ColumnWidth from the menu bar. Then type in a new width and press enter. 2. Using the mouse, position the pointer at the right-most end of the column you wish to re-size (in the column header area where the letters are). Your pointer will turn into a vertical bar with two small arrows on either side. You can then drag and drop to the desired column width. 3. Double-click on the right-most edge of the column header. Row Height In the same respect, some of the data you enter will not fit the height of the cell and/or row it is in. In order to change the row height, follow the following steps: 7 The Difference Between Relative and Absolute References Relative references When you create a formula, references to cells or ranges are usually based on their position relative to the cell that contains the formula. In the following example, cell B6 contains the formula =A5; Microsoft Excel finds the value one cell above and one cell to the left of B6. This is known as a relative reference. A B 5 100 6 200 =A5 7 When you copy a formula that uses relative references, Excel automatically adjusts the references in the pasted formula to refer to different cells relative to the position of the formula. In the following example, the formula in cell B6, =A5, which is one cell above and to the left of B6, has been copied to cell B7. Excel has adjusted the formula in cell B7 to =A6, which refers to the cell that is one cell above and to the left of cell B7. A B 5 100 6 200 =A5 7 =A6 Absolute references If you don't want Excel to adjust references when you copy a formula to a different cell, use an absolute reference. For example, if your formula multiplies cell A5 with cell C1 (=A5*C1) and you copy the formula to another cell, Excel will adjust both references. You can create an absolute reference to cell C1 by placing a dollar sign ($) before the parts of the reference that do not change. To create an absolute reference to cell C1, for example, add dollar signs to the formula as follows: =A5*$C$1 Switching between relative and absolute references If you created a formula and want to change relative references to absolute (and vice versa), select the cell that contains the formula. In the formula bar, select the reference you want to change and then press F4. Each time you press F4, Excel toggles through the combinations: absolute column and absolute row (for example, $C$1); relative column and absolute row (C$1); absolute column and relative row ($C1); and relative column and relative row (C1). For example, if you select the address $A$1 in a formula and press F4, the reference becomes A$1. Press F4 again and the reference becomes $A1, and so on. 10 Creating charts Charts can emphasize important points or trends in your data and make them easier to understand. Using charts, you are able to get your point across efficiently and quickly, embedding them in reports or presenting them to interested audiences. What do different graphs represent? The following table illustrates what some of the different graphs illustrate. Name Description of Use Column Compares values across categories Bar Compares values across categories Line Displays a trend over time Pie Displays parts of the whole XY (Scatter) Compares pairs of values Area Shows the trends of value over time Table 2 To create a chart, you must first have data in your worksheet. Included with this data, it is helpful to have labels in the column to the left of the data to indicate categories, labels across the row above the data that indicate the type of data or the time over which the data will be analyzed, data all formatted the same way, and data in cells that are next to each other. Figure 7 First, determine the type of chart that will display the data most effectively. Second, select the cells that contain the data that you want charted – this is the data range. Chart Wizard Click the Chart Wizard button (circled in Figure 7) from the standard toolbar. The wizard will then open up and prompt you for choosing chart types, data ranges, plotting methods, titles, legend placement, and chart placement. 1. Choose the type of chart you would like to create; Click Next 2. Make sure the chart looks like you expect it to; if not, you may need to tell Excel to analyze the data in rows instead of columns or vice-versa; Click Next 3. The third step has a series of tabs with options for adjusting how your chart looks; Click Next after you have adjusted options on all desired tabs 11 a. Titles: type a meaningful heading in any desired area (for instance, a chart title may not be sufficient, but the axes may need to be labeled as well) b. Axes: select or deselect showing the axis values c. Gridlines: select or deselect the gridlines on the chart to make it easier to read d. Legend: choose whether or not to show the chart legend and where to place it e. Data Labels: choose whether to include data labels, values, percents, etc. f. Data Table: choose whether or not to include the table of values from your worksheet 4. The final step is to select where to place the chart; select As a New Sheet [Chart 1] for the chart to be placed on a new worksheet in your workbook or select As Object In [Sheet1] for the chart to be placed within a spreadsheet. Selecting As a New Sheet will yield a chart that is easier to export to other applications such as MS Word or PowerPoint Formatting the Chart Once your chart is created, you may decide there are some things you need to change about how it looks or how the data are displayed. Scale: To adjust the scale of the chart for bar or line graphs, highlight the axis to adjust and go to Format→Selected Axis (or double-click on the selected axis). Depending on which axis you select, you’ll get different options. Typically the x-axis (vertical) is the one you’ll want to adjust. You can uncheck the “Auto” boxes and set the values at your own levels. Minimum is the lowest value displayed on the x-axis. Typically, this is zero, but it may at times be negative or you may want to start it at 1000, depending on how your data are distributed. Maximum is the highest value displayed, and is usually set at the most logical value based on your highest data point. You may want to adjust this value in order to change the distribution of the points on the graph. Major and minor unit refer to how the gridlines are displayed on the chart and how the numbers are displayed on the x-axis. If the major unit is 10, then the values on the axis will be something like: 10, 20, 30, 40, 50; unless you have minor gridlines shown (an option in the chart wizard), then the minor unit value will not affect the chart appearance. Colors, Patterns and Fonts To make your chart even more stunning visually, you can adjust the colors of the background, foreground, borders, fonts, axes, bars, lines, pie slices, etc., etc. Just double-click on the object you want to format and the color palette will open for you to express your artistic creativity. Patterns come in useful when you are relying on black-and-white displays of multiple parameters because you can more easily distinguish one bar or one line from another. When working with Pie Charts, be careful to select the piece of the pie you to which you want to apply a color or pattern (the first click will select the pie itself, the second click will select a piece of the pie) and then double-click on it. Otherwise you will change the color or pattern for the entire pie instead of each piece. You can also adjust the size and style of the font for different pieces of your chart by double-clicking on the desired text or section. (Note: if you change the font for a value on the x-axis, for instance, all values on the x-axis will change formatting). 3-D Charts: If you have created a chart using a 3-D chart type, you can modify the angles at which the chart is portrayed. Click once on the Chart so the black handles are selecting the entire chart. Go to Chart→3-D View to change the depth or angle. 12