1. Pareto Chart Excel Pivot Table

Switch Row/Column If you want to display the animals (instead of the months) on the horizontal axis, execute the following steps. Select the chart. On the Design tab, in the Data group, click Switch Row/Column. Result: Legend Position To move the legend to the right side of the chart, execute the following steps. Select the chart. Click the + button on the right side of the chart, click the arrow next to Legend and click Right. Result: Data Labels You can use data labels to focus your readers' attention on a single data series or data point.

Select the chart. Click a green bar to select the Jun data series. Use your arrow keys to select the population of Dolphins in June (tiny green bar). Click the + button on the right side of the chart and click the check box next to Data Labels.

Frank Rice Microsoft Corporation June 2004 Applies to: Microsoft® Office Excel 2003 Microsoft Visual Studio®.NET Summary: Charts provide a powerful way to visually represent series data that might otherwise be difficult to analyze. This article details methods of creating charts though the macro recorder, using Microsoft Visual Basic for Applications, and by using automation. (11 printed pages) Contents Introduction Microsoft® Office Excel 2003 includes a powerful and versatile charting engine. You can choose from a large variety of business and technical chart types.

View Notes - How to make Pareto Chart from ECO EVO 208 at UC Irvine. Creating Pareto Charts with Microsoft Excel Creating the Initial Pareto Chart Before getting.

In addition, you can enhance the appearance of your charts with items such as lines, arrows, pictures, and so forth. In Excel, the chart represents one of the most widely used components for business applications.

Essentially, a chart visually presents a table of numbers. Displaying data in a well thought out chart can make your data more understandable and can help you quickly get your point across during a presentation. Because a chart is a visual representation of your data, it is particularly useful for understanding a relatively long series of numbers and their relationships. With a chart, you can identify trends and patterns that would be difficult to identify just in a series of numbers.

This article looks at:. Creating a chart object on separate sheets. Creating embedded chart objects in worksheets. Moving charts between worksheets and chart sheets. Creating a chart through automation Overview In Excel, you can create two types of charts; charts that occupy their own chart sheets, and charts that are embedded in a worksheet. The Chart object, which is a chart that exists as the only object in a chart sheet, is a member of the worksheet's Charts collection. The embedded chart, on the other hand, is contained in a worksheet that can contain other objects.

The embedded chart is contained in the ChartObject object. The ChartObject is a member of the worksheet's ChartObjects collection and acts as a container for Chart objects. Properties and methods for the ChartObject object control the appearance and size of the embedded chart on the worksheet. The ChartObjects collection contains all the embedded charts on a single sheet. Note The ChartObject object is also a member of the Shapes collection. You can use the Shapes collection as an alternative way to refer to embedded charts on a worksheet.

Using Chart Sheets The simplest way to create a chart in a chart sheet is to select your data and press F11. Your data is sent to whatever chart style is the default, placing the result on a new chart sheet. You can always modify the default chart type by clicking the Chart menu and then clicking Chart Type. Using techniques that are discussed later, you can also move the chart to a worksheet. The following sections describe other ways to create charts in chart sheets programmatically. Adding Chart to a Chart Sheet with the Macro Recorder Before creating the chart, start the macro recorder with the following steps:.

Start Excel. In the blank default worksheet, add the following data. A B C D 1 Tools Sold Oct Nov Dec 2 South 7000 6610 4827 3 North 1155 2914 3790 4 East 757 659 7072 5 West 5012 3880 4752. To start the macro recorder, on the Tools menu, point to Macro, and then click Record New Macro.

In the Macro name box, type a name for the macro such as ChartMacro. In the Store macro in box, click the location where you want to store the macro. Note If you want a macro to be available whenever you use Excel, select Personal Macro Workbook. Now create a chart based on the table of data as follows:.

Select the cells that contain the data you want to use for your chart. In this case A3:D7. On the Insert menu, click Chart to start the Chart Wizard. In Step 1 of the wizard, select the Column option and click Next. In Step 2, choose the Series in Rows option and click Next. In Step 3, type Tools Sales for Qtr 1 in the Chart Title box. Type Month in the Category (X) axis box and Sales in the Category (Y) axis box and click Next.

In Step 4, choose the As new sheet option and type the name of the chart sheet as Tools Sold and then click Finish. To stop the macro recorder, on the Stop Recording toolbar, click Stop Recording. To review the recorded macro, on the Tools menu, click Macro, and then click Macros. In the Macro dialog box, click ChartMacro, and then click Edit. The recorded macro should look like the following.

Sub AddChartSheet Dim chtChart As Chart 'Create a new chart. Set chtChart = Charts.Add With chtChart.Name = 'Tool Sales2'.ChartType = xlColumnClustered 'Link to the source data range.SetSourceData Source:=Sheets('Sheet1').Range('A1:D5')PlotBy:=xlRows.HasTitle = True.ChartTitle.Text = '=Sheet1!R1C2'.Axes(xlCategory, xlPrimary).HasTitle = True.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = 'Month'.Axes(xlValue, xlPrimary).HasTitle = True.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = 'Sales' End With End Sub In this code, the Location method is removed since it isn't required. A chart sheet is created by default. Notice that the chart title has been defined as a formula. The Chart Wizard, on the other hand, doesn't allow you to enter a formula. The remainder of the code is just like that created by the macro recorder. Note Data entered as a formula in the chart Text property must be defined as the R1C1 address method and not as the A1 address method.

Using Embedded Charts A chart embedded on a worksheet is an object, just like an embedded graphic, control, or anything else added to your worksheet. Note One thing to remember about working with an embedded chart object is to select it as an object. To do this you need to hold down the CTRL key while clicking the chart. Otherwise, you are selecting an element of the chart instead of the entire object. To verify this, white handles appear around the perimeter of the chart and the name of the chart is in the name box. Black handles around the chart indicate that you have selected an element of the chart. Adding an Embedded Chart with the Macro Recorder.

Start Excel. Click Sheet1 in the default workbook (or the worksheet that contains your data).

Start the macro recorder by clicking the Tools menu, pointing to Macro, and then clicking Record New Macro. In the Macro name box, type a name for the macro such as EmbeddedChart. In the Store macro in box, click the location where you want to store the macro. Note If you want a macro to be available whenever you use Excel, select Personal Macro Workbook. Now create a chart based on the table of data as follows:. Select the cells that contain the data you want to use for your chart. In this case A1:D5.

On the Insert menu, click Chart to start the Chart Wizard. In Step 1 of the wizard, select the Column option and click Next. In Step 2, choose the Series in Rows option and click Next. In Step 3, type Tools Sales for Qtr 1 in the Chart Title box. Type Month in the Category (X) axis box and Sales in the Category (Y) axis box and click Next.

In Step 4, choose the As object in option and type the name of the chart sheet (such as Sheet1) and then click Finish. To stop the macro recorder, on the Stop Recording toolbar, click Stop Recording. To review the recorded macro, on the Tools menu, click Macro, and then click Macros. In the Macro dialog box, click EmbeddedChart (or the name of your macro), and then click Edit. The recorded macro should look similar to the following: Note This code relies on the data created in the section of this article titled. Range('A1:D5').Select Charts.Add ActiveChart.ChartType = xlColumnClustered 'Link to the source data range.

ActiveChart.SetSourceData Source:=Sheets('Sheet1').Range('A1:D5'), PlotBy:= xlRows ActiveChart.Location Where:=xlLocationAsObject, Name:='Sheet1' With ActiveChart.HasTitle = True.ChartTitle.Characters.Text = 'Tools Sales for Qtr 1'.Axes(xlCategory, xlPrimary).HasTitle = True.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = 'Month'.Axes(xlValue, xlPrimary).HasTitle = True.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = 'Sales' End With This recorded macro code is similar to that created for the chart added to the chart sheet including the definition of the chart. The exception is that it uses the Location method to define the chart as embedded. Adding an Embedded Chart with VBA Code The following VBA subroutine uses the Parent property of the embedded chart to identify the ChartObject object: Note This code relies on the data created in the section of this article titled. Sub AddChart Dim chtChart As Chart ActiveSheet.ChartObjects.Delete 'Create a new chart. Set chtChart = Charts.Add Set chtChart = chtChart.Location(Where:=xlLocationAsObject, Name:='Sheet1') With chtChart.ChartType = xlColumnClustered 'Set data source range.SetSourceData Source:=Sheets('Sheet1').Range('A1:D5'), PlotBy:= xlRows.HasTitle = True.ChartTitle.Text = '=Sheet1!R1C1' 'The Parent property is used to set properties of 'the Chart. With.Parent.Top = Range('F9').Top.Left = Range('F9').Left.Name = 'ToolsChart2' End With End With End Sub The first thing that happens in this procedure is that any existing ChartObjects objects are deleted. Next the chtChart object variable is set to refer to the added chart.

By default, the new chart is located on a chart sheet, so the Location method is used to define the chart as an embedded chart. Then the chart type, source data, and chart title are defined. Notice the use of the Parent property. The ChartObject object is a container for the Chart object.

The ChartObject object has properties and methods for controlling the appearance and size of the embedded chart on the worksheet. The Parent property is used to set such properties as the Top and Left of the ChartObject object which affect the Chart object.

Moving Charts between Chart Sheets and Worksheets As stated earlier, an Excel chart can appear embedded in a worksheet or reside in a separate chart sheet. We demonstrated two ways to store charts on a chart sheet. The following procedure demonstrates how to store multiple charts on a single chart sheet. Use this routine to create the embedded charts, placing them in a worksheet. Note Putting multiple charts on a single chart sheet lets you use the View menu, Sized with Window option (available when the chart sheet is active) to scale the charts to the window size and dimensions.

This code relies on the data created in the section of this article titled. Also, this is the same code as the previous AddChart routine with the Delete method removed. How to crack quick heal terminator. Create the first embedded chart. Sub AddChart Dim chtChart As Chart 'Create a new chart. Set chtChart = Charts.Add Set chtChart = chtChart.Location(Where:=xlLocationAsObject, Name:='Sheet1') With chtChart.ChartType = xlColumnClustered 'Set the source data range for the chart.SetSourceData Source:=Sheets('Sheet1').Range('A1:D5'), PlotBy:= xlRows.HasTitle = True.ChartTitle.Text = '=Sheet1!R1C1' With.Parent.Top = Range('F3').Top.Left = Range('F3').Left.Name = 'ToolsChart1' End With End With End Sub. Change the coordinates of the top and left location of the chart and the name of the chart with the existing structure to create the second embedded chart. With.Parent.Top = Range('F19').Top.Left = Range('F19').Left.Name = 'ToolsChart2' End With.

Select any blank cell the worksheet and press F11; this creates an empty chart sheet to hold the embedded charts. Reactivate your worksheet, click an embedded chart, and click the Chart menu and then click Location to display the Chart Location dialog box. Choose As object in and specify the empty chart sheet and click OK.

Excel will transfer the embedded chart to your chart sheet. Select your remaining chart, and then click the Chart menu and Location to move it to the chart sheet using steps 4 and 5.

Now you can arrange and size the charts any way you like. To move a chart from a chart sheet to a worksheet, click the chart in the chart sheet to activate it, click the Chart menu, and then click Location. Next, click the As object in option, select the worksheet from the drop-down list, and click OK.

The chart is moved to the specified worksheet and deleted from the chart sheet. Using Automation to Create an Embedded Chart This section illustrates the code that uses automation in a Microsoft Visual Basic® program to create an Excel workbook that contains a chart embedded on a worksheet using the following steps:. Start Microsoft Visual Studio.NET. On the File menu, click New, and then click Project.

Select Windows Application from the Visual Basic Projects types. Form1 is created by default.

Add a reference to the Excel object library. To do this, follow these steps:. On the Project menu, click Add Reference.

On the COM tab, locate Microsoft Excel 11.0 Object Library, and then click Select. Click OK in the Add References dialog box to accept your selections. If you receive a prompt to generate wrappers for the libraries that you selected, click Yes. On the View menu, click ToolBox. Add a combo box and a button to Form1. Double-click Button1 to generate a definition for the button's Click event handler.

Pareto Chart Excel Pivot Table

Add the following code to the Button1Click event procedure.