Excel For Beginners- Week 5 PDF

EXCEL FOR BEGINNERS- WEEK 5 EXCEL 2019 Getting Started with Excel Charts In this week’s class we will cover the following:  How Excel handles charts  The parts of a chart  The basic steps for creating a chart  Working with ch...

EXCEL FOR BEGINNERS- WEEK 5 EXCEL 2019 Getting Started with Excel Charts In this week’s class we will cover the following:  How Excel handles charts  The parts of a chart  The basic steps for creating a chart  Working with charts  Looking at examples of chart types Charts offer a visual representation of numeric values; they are at-a-glance views that allow you to specify relationships between data values, point out differences, and observe business trends. Few mechanisms allow you to absorb data faster than a chart, which can be a key component in your dashboard. While most people think of a spreadsheet product such as Excel, they think of crunching rows and columns of numbers. But Excel is no slouch when it comes to presenting data visually in the form of a chart. In this chapter, we present an overview of Excel’s charting capabilities and show you how to create and customize your own charts using Excel. What Is a Chart? Let’s start with the basics. A chart is a visual representation of numeric values. Charts (also known as graphs) have been an integral part of spreadsheets since the early days of Lotus 1-2-3. Charts generated by early spreadsheet products were extremely crude by today’s standards. Over the years, however, the quality and flexibility have improved significantly. You’ll find that Excel provides you with the tools to create a wide variety of highly customizable charts that can help you effectively communicate your message. Displaying data in a well-conceived chart can make your numbers more understandable. Because a chart presents a picture, charts are particularly useful for summarizing a series of numbers and their interrelationships. Making a chart can often help you spot trends and patterns that might otherwise go unnoticed. The figure below shows a worksheet that contains a simple column chart that depicts a company’s sales volume by month. Viewing the chart makes it apparent that sales were off in the summer months (June through August), but they increased steadily during the final four months of the year. You could, of course, arrive at this same conclusion simply by studying the numbers. But viewing the chart makes the point much more quickly. A column chart is just one of many different types of charts that you can create with Excel. By the way, creating this chart is simple: select the data in A1:B13, and press Alt+F1. How Excel handles charts Before you can create a chart, you must have some numbers (data). The data, of course, is stored in the cells in a worksheet. Normally, the data that is used by a chart resides in a single worksheet, but that’s not a strict requirement. A chart can use data that’s stored in any number of worksheets, and the worksheets can even be in different workbooks. The decision to use data from one sheet or multiple sheets really depends on your data model, the nature of your data sources, and the interactivity that you want to give your dashboard. A chart is essentially an “object” that Excel creates upon request. This object consists of one or more data series, displayed graphically. The appearance of the data series depends on the selected chart type. For example, if you create a line chart that uses two data series, the chart contains two lines, and each line represents one data series. The data for each series is stored in a separate row or column. Each point on the line is determined by the value in a single cell and is represented by a marker. You can distinguish the lines by their thickness, line style, color, or data markers. The figure below shows a line chart that plots two data series across a nine- year period. The series are identified by using different data markers (squares versus circles), shown in the legend at the bottom of the chart. The lines also use different colors, which is not apparent in the grayscale figure. This line chart displays two data series. A key point to keep in mind is that charts are dynamic. In other words, a chart series is linked to the data in your worksheet. If the data changes, the chart is updated automatically to reflect those changes so that your dashboard can show the most current information. After you’ve created a chart, you can always change its type, change the formatting, add new data series to it, or change an existing data series so that it uses data in a different range. Charts can reside in either of two locations in a workbook: On a worksheet (an embedded chart) On a separate chart sheet Embedded charts An embedded chart basically floats on top of a worksheet, on the worksheet’s drawing layer. The charts shown previously in this chapter are both embedded charts. As with other drawing objects (such as a text box or a shape), you can move an embedded chart, resize it, change its proportions, adjust its borders, and add effects such as a shadow. Using embedded charts enables you to view the chart next to the data that it uses. Alternatively, you can place several embedded charts together so that they print on a single page. When you create a chart, it always starts off as an embedded chart. The exception to this rule is when you select a range of data and press F11 to create a default chart. Such a chart is created on a chart sheet. To make any changes to the actual chart in an embedded chart object, you must click it to activate the chart. When a chart is activated, Excel displays the two Chart Tools contextual tabs shown in the figure below. Chart Sheets You can move an embedded chart to its own chart sheet so that you can view it by clicking a sheet tab. When you move a chart to a chart sheet, the chart occupies the entire sheet. If you plan to print a chart on a page by itself, using a chart sheet is often your better choice. If you have many charts to create, you may want to put each one on a separate chart sheet to avoid cluttering your worksheet. This technique also makes locating a particular chart easier because you can change the names of the chart sheets’ tabs to provide a description of the chart that it contains. Although chart sheets are not typically used in traditional dashboards, they can come in handy when producing reports that will be viewed in a multitab workbook. The figure shows a chart on a chart sheet. When a chart sheet is activated, Excel displays the Chart Tools contextual tabs, as described in the previous section. FIGURE: A chart on a chart sheet Parts of a chart A chart is made up of many different elements, and all of these elements are optional. Yes, you can create a chart that contains no chart elements—an empty chart. It’s not very useful, but Excel allows it. Refer to the following chart as you read the following description of the chart’s elements. FIGURE: Parts of a chart This particular chart is a combination chart that displays both columns and a line. The chart has two data series: Income and Profit Margin. Income is plotted as vertical columns, and Profit Margin is plotted as a line with square markers. Each bar (or marker on the line) represents a single data point (the value in a cell). 20 The chart has a horizontal axis, known as the category axis. This axis represents the category for each data point (January, February, and so on). This axis doesn’t have a label because the category units are obvious. Notice that this chart has two vertical axes. These are known as value axes, and each one has a different scale. The axis on the left is for the column series (Income), and the axis on the right is for the line series (Profit Margin). The value axes also display scale values. The axis on the left displays scale values from 0 to 250,000, in major unit increments of 50,000. The value axis on the right uses a different scale: 0 percent to 14 percent, in increments of 2 percent. For a value axis, you can control the minimum and maximum values, as well as the increment value. A chart with two value axes is appropriate because the two data series vary dramatically in scale. If the Profit Margin data was plotted using the left axis, the line would not even be visible. If a chart has more than one data series, you’ll usually need a way to identify the data series or data points. A legend, for example, is often used to identify the various series in a chart. In this example, the legend appears at the bottom of the chart. Some charts also display data labels to identify specific data points. The example chart displays data labels for the Profit Margin series, but not for the Income series. In addition, most charts (including the example chart) contain a chart title and additional labels to identify the axes or categories. The example chart also contains horizontal gridlines (which correspond to the values on the left axis). Gridlines are basically extensions of the value axis scale, which makes it easier for the viewer to determine the magnitude of the data points. In addition, all charts have a chart area (the entire background area of the chart) and a plot area (the part that shows the actual chart, including the plotted data, the axes, and the axis labels). Charts can have additional parts or fewer parts, depending on the chart type. For example, a pie chart has “slices” and no axes. A 3-D chart may have walls and a floor. Several other types of items can be added to a chart. For example, you can add a trend line or display error bars. Chart limitations As with most features in Excel, charts do have limits on the amount of data that they can handle and present. Table 20.1 lists the limitations of Excel charts. Item Limitation Charts in a worksheet Limited by available memory Worksheets referred to by a chart 255 Data series in a chart 255 Data points in a data series 32,000 Data points in a data series (3-D charts) 4,000 Total data points in a chart 256,000 Basic Steps for Creating a Chart Creating a chart is relatively easy. The following sections describe how to create and then customize a basic chart to best communicate your business goals. Creating the chart Follow these general steps to create a chart using the data in the figure below: 1. Select the data you want to use in the chart. Make sure you select the column headers if the data has them (in this example, you would select A1:C4). Another option is to select a single cell within a range of data. Excel then uses the entire data range for the chart. This data would make a good chart. 2. Click the Insert tab and then click a Chart icon in the Charts group. The icon expands into a gallery list that shows chart subtypes for the selected chart type. 3. Click a Chart subtype, and Excel then creates the chart of the specified type. To create a default chart quickly, select the data and press Alt+F1 to create an embedded chart, or press F11 to create a chart on a chart sheet. Switching the row and column orientation When Excel creates a chart, it uses an algorithm to determine whether the data is arranged in columns or in rows. Most of the time Excel guesses correctly, but if it creates the chart using the wrong orientation, you can quickly change it by selecting the chart and choosing Chart Tools Design ➪ Data ➪ Switch Row/Column. This command is a toggle, so if changing the data orientation doesn’t improve the chart, just choose the command again (or click the Undo button found on the Quick Access toolbar). 20 The orientation of the data has a drastic effect on the look (and, perhaps, understandability) of your chart. The figure below shows the column chart in the figure above after changing the orientation. Notice that the chart now has three data series, one for each month. If the goal of your dashboard is to compare actual with projected values for each month, this version of the chart is much more difficult to interpret because the relevant columns are not adjacent. Changing the chart type After you’ve created a chart, you can easily change the chart type. Although a column chart may work well for a particular data set, there’s no harm in checking out some other chart types. You can choose Chart Tools Design ➪ Type ➪ Change Chart Type to display the Change Chart Type dialog box and experiment with other chart types. The figure below shows the Change Chart Type dialog box. If your chart uses more than one data series, make sure that a chart element other than a data series is selected when you choose the Chart Tools Design ➪ Type ➪ Change Chart Type command. If you select a series, the command changes the chart type of the selected series only. In the Change Chart Type dialog box, the main categories are listed on the left, and the subtypes are shown as icons. Select an icon and click OK, and Excel displays the chart using the new chart type. If you don’t like the result, click the Undo button. group. In fact, this method is more efficient because it doesn’t involve a dialog box. Applying a chart layout Each chart type has a number of prebuilt layouts that you can apply with a single mouse click. A layout contains additional chart elements, such as a title, data labels, axes, and so on. This step is optional, but one of the prebuilt designs might be just what you need. Even if the layout isn’t exactly what you want, it may be close enough that you need to make only a few adjustments. To apply a layout, select the chart and use the Chart Tools Design ➪ Chart Layouts ➪ Quick Layout gallery. The figure below shows how a column chart would look using various layouts. Applying a chart style The Chart Tools Design ➪ Chart Styles gallery contains quite a few styles that you can apply to your chart. The styles consist of various color choices and some special effects. Again, this step is optional. Adding and deleting chart elements In some cases, applying a chart layout or chart style (as described previously) gives you a chart with all of the elements that you need. Most of the time, however, you’ll need to add or remove some chart elements and fine-tune the layout. You do this using the controls on the Chart Tools ➪Design and Chart Tools ➪ Format tabs. For example, to give a chart a title, choose Chart Tools ➪ Design Add Chart Element ➪ Chart Title. The control displays some options that determine where the title is placed. Excel inserts a title with the text “Chart Title.” Click the text and replace it with your actual chart title. Formatting chart elements Every element in a chart can be formatted and customized in many ways. Excel provides two ways to format and customize individual chart elements. Each of the following methods require that you select the chart element first: Use the Ribbon controls on the Chart Tools Format tab. Press Ctrl+1 to display the Format task pane that’s specific to the selected chart element. You can also double-click a chart element to display the Format task pane for the element. For example, assume that you’d like to change the color of the columns for one of the series in the chart. Click any column in the series (which selects the entire series). Then choose Chart Tools Format ➪ Shape Styles ➪ Shape Fill, and choose a color from the displayed list. To change the properties of the outline around the columns, use the Chart Tools Format ➪ Shape Styles ➪ Shape Outline control. To change the effects used in the columns (for example, to add a shadow), use the Chart Tools Format ➪ Shape Styles ➪ Shape Effects control. Alternatively, you can select a series in the chart, press Ctrl+1, and use the Format Data Series task pane, as shown in Figure 20.14. Note that this is a tabbed pane. Click a tab or icon along the top and then expand a section on the left side to view additional controls. It’s also a docked pane, so you can click another element in the chart. In other words, you don’t have to close the task pane to see the changes you specify. FIGURE: Using the Format Data Series task pane Modifying and Customizing Charts The sections that follow cover common chart modifications. chart sheet, just click its sheet tab. Moving and resizing a chart If your chart is an embedded chart, you can freely move and resize it with your mouse. Click the chart’s border and then drag the border to move the chart. Drag any of the eight “handles” to resize the chart. The handles consist of white circles that appear on the chart’s corners and edges when you click the chart’s border. When the mouse pointer turns into a double arrow, click and drag to resize the chart. When a chart is selected, you can use the Chart Tools Format ➪ Size controls to adjust the height and width of the chart. Use the spinners, or type the dimensions directly into the Height and Width controls. Oddly, Excel does not provide similar controls to specify the top and left positions of the chart. To move an embedded chart, just click its border at any location except one of the eight resizing handles. Then drag the chart to its new location. You also can use standard cut and- paste techniques to move an embedded chart. Select the chart and choose Home ➪ Clipboard ➪ Cut (or press Ctrl+X). Then activate a cell near the desired location and choose Home ➪ Clipboard ➪ Paste (or press Ctrl+V). The new location can be in a different worksheet or even in a different workbook. If you paste the chart to a different workbook, it will be linked to the data in the original workbook. Another way to move a chart to a different location is to choose Chart Tools Design ➪ Location ➪ Move Chart. This command displays the Move Chart dialog box, which lets you specify a new sheet for the chart (either a chart sheet or a worksheet). Converting an embedded chart to a chart sheet When you create a chart using the icons in the Insert ➪ Charts group, the result is always an embedded chart. If you’d prefer that your chart be located on a chart sheet, you can easily move it. To convert an embedded chart to a chart on a chart sheet, select the chart and choose Chart Tools Design ➪ Location ➪ Move Chart to display the Move Chart dialog box shown in the figure shown. Select the New Sheet option and (optionally) provide a different name for the chart sheet. To convert a chart on a chart sheet to an embedded chart, activate the chart sheet and then choose Chart Tools Design ➪ Location ➪ Move Chart to display the Move Chart dialog box. Select the Object In option and specify the sheet by using the drop-down control. Copying a chart To make an exact copy of an embedded chart, select the chart and choose Home ➪Clipboard ➪ Copy (or press Ctrl+C). Then activate a cell near the desired location and choose Home ➪ Clipboard ➪ Paste (or press Ctrl+V). The new location can be in a different worksheet or even in a different workbook. If you paste the chart to a different workbook, it will be linked to the data in the original workbook. To copy a chart on a chart sheet, press Ctrl while you drag the sheet tab to a new location in the tab list. Deleting a chart To delete an embedded chart, simply click the chart and then press Delete. When the Ctrl key is pressed, you can select multiple charts and then delete them all with a single press of the Delete key. To delete a chart sheet, right-click its sheet tab and choose Delete from the shortcut menu. To delete multiple chart sheets, select them by pressing Ctrl while you click the sheet tabs. Adding chart elements To add new elements to a chart (such as a title, legend, data labels, or gridlines), activate the chart and use the controls in the Chart Elements + icon, which appears to the right of the chart. Note that each item expands to display additional options. You can also use the Add Chart Element control on the Chart Tools Design tab, in the Chart Layouts group. Moving and deleting chart elements Some elements within a chart can be moved: titles, legend, and data labels. To move a chart element, simply click it to select it and then drag its border. The easiest way to delete a chart element is to select it and then press Delete. You can also use the controls on the Chart Elements icon, which appears to the right of the chart, to reposition chart elements. name. Printing charts Printing embedded charts is nothing special; you print them the same way that you print a worksheet. As long as you include the embedded chart in the range that you want to print, Excel prints the chart as it appears on- screen. When printing a sheet that contains embedded charts, it’s a good idea to preview first (or use Page Layout view) to ensure that your charts don’t span multiple pages. If you created the chart on a chart sheet, Excel always prints the chart on a page by itself. If you don’t want a particular embedded chart to appear on your printout, access the Format Chart Area task pane and select the Size & Properties icon. Then expand the Properties section and clear the Print Object check box.