how to create a chart in excel for mac 2011 using only certain rows of data

In the Name Manager dialog box, specify the name as ChartMonths and enter the following formula in Refers to part: OFFSET(FormulaA2,,,COUNTIF(FormulaA2:A100, )) Click Ok.

In case you work with charts that are frequently updated, its better to create a dynamic chart range.

This Tutorial Covers: What is a Dynamic Chart Range How to Create a Dynamic Chart Range in Excel Using Excel Table Using Excel Formulas Step 1 Creating Dynamic Named Ranges Step 2 Create a Chart Using these Named Ranges What is a Dynamic Chart Range A dynamic chart range is a data range that updates automatically when you change the data source.

This dynamic range is then used as the source data in a chart.

As the data changes, the dynamic range updates instantly which leads to an update in the chart.

Below is an example of a chart that uses a dynamic chart range.

Note that the chart updates with the new data points for May and June as soon as the data in entered.

How to Create a Dynamic Chart Range in Excel There are two ways to create a dynamic chart range in Excel: Using Excel Table Using Formulas In most of the cases, using Excel Table is the best way to create dynamic ranges in Excel.

Using Excel Table Using Excel Table is the best way to create dynamic ranges as it updates automatically when a new data point is added to it.

Excel Table feature was introduced in Excel 2007 version of Windows and if youre versions prior to it, you wont be able to use it (see the next section on creating dynamic chart range using formulas).

In the example below, you can see that as soon as I add new data, the Excel Table expands to include this data as a part of the table (note that the border and formatting expand to include it in the table).

Now, we need to use this Excel table while creating the charts.

Here are the exact steps to create a dynamic line chart using the Excel table: Select the entire Excel table.

Thats it The above steps would insert a line chart which would automatically update when you add more data to the Excel table.

Note that while adding new data automatically updates the chart, deleting data would not completely remove the data points.

For example, if you remove 2 data points, the chart will show some empty space on the right.

To correct this, drag the blue mark at the bottom right of the Excel table to remove the deleted data points from the table (as shown below).

While I have taken the example of a line chart, you can also create other chart types such as columnbar charts using this technique.

Using Excel Formulas As I mentioned, using Excel table is the best way to create dynamic chart ranges.

However, if you cant use Excel table for some reason (possibly if you are using Excel 2003), there is another (slightly complicated) way to create dynamic chart ranges using Excel formulas and named ranges.

Suppose you have the data set as shown below: To create a dynamic chart range from this data, we need to: Create two dynamic named ranges using the OFFSET formula (one each for Values and Months column).

Addingdeleting a data point would automatically update these named ranges.

Step 1 Creating Dynamic Named Ranges Below are the steps to create dynamic named ranges: Go to the Formulas Tab.

In the Name Manager dialog box, specify the name as ChartValues and enter the following formula in Refers to part: OFFSET(FormulaB2,,,COUNTIF(FormulaB2:B100, )) Click OK.

How To Create A Chart In Excel For Mac 2011 Using Only Certain Rows Of Data