1. Split Data into Several Columns Based on Carriage Returns
2.Update Charts Automatically When You Enter New Data
I have a Question by myself?. I use an Excel workbook to track and chart daily sales. Since the number of data points changes every day, I have to update the chart manually so it includes the new data. How do I get the chart range to expand automatically?
When you select a chart series in Excel 97 or later, the ranges used by the series are outlined on the worksheet. You could simply drag a corner of the outline to extend the range.
This document describes another approach that uses formulas to define the ranges used in a chart. The steps listed below describe how to create dynamic ranges for the chart shown in figure below.
Here, I have several address blocks containing carriage returns. I’d like to parse these addresses so that they go across several columns.
So I highlight the column and fire the Text to Columns command on the Data tab of the Ribbon.
After selecting Delimited and the Next button, I get this dialog box.
As you can see in the Data Preview, Text to Columns initially just recognizes the first line of each address block.
So I Click the Other Delimiter and enter Ctrl+J. The field looks blank, but you can immediately see that Excel brings in the rest of the address.
Now to parse the city state and zip, I simply click the Comma delimiter.
The reward for my efforts is a set of columns that contain my parsed addresses.
So I highlight the column and fire the Text to Columns command on the Data tab of the Ribbon.
After selecting Delimited and the Next button, I get this dialog box.
As you can see in the Data Preview, Text to Columns initially just recognizes the first line of each address block.
So I Click the Other Delimiter and enter Ctrl+J. The field looks blank, but you can immediately see that Excel brings in the rest of the address.
Now to parse the city state and zip, I simply click the Comma delimiter.
The reward for my efforts is a set of columns that contain my parsed addresses.
I hope This will be beneficial for You!!! :-)
2.Update Charts Automatically When You Enter New Data
I have a Question by myself?. I use an Excel workbook to track and chart daily sales. Since the number of data points changes every day, I have to update the chart manually so it includes the new data. How do I get the chart range to expand automatically?
When you select a chart series in Excel 97 or later, the ranges used by the series are outlined on the worksheet. You could simply drag a corner of the outline to extend the range.
This document describes another approach that uses formulas to define the ranges used in a chart. The steps listed below describe how to create dynamic ranges for the chart shown in figure below.
STEPS:
1.Enter the data and create the chart shown in the figure.
2.Select Insert, Name, Define to bring up the Define Name dialog box.
3.In the 'Names in workbook' field, enter Date. In the 'Refers to' field, enter this formula:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1! $A:$A)-1)
4.Click Add to create the name. Notice that the OFFSET function refers to the first data point (cell A2) and uses the COUNTA function to get the number of data points in the column. Because column A has a heading in row 1, the formula subtracts 1 from the number.
5.Now type Sales in 'Names in workbook', and in 'Refers to' enter this formula:
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)
7.Click Add, and then OK to close the dialog box.
Activate the chart and select the data series. In this example, the (unmodified) formula in the formula bar will read:
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$10, Sheet1!$B$2:$B$10,1)
8.Replace the range references in the SERIES formula with the names you defined in steps 4 and 5. The SERIES formula should read:
=SERIES(,Sheet1!Date,Sheet1!Sales,1)
After performing these steps, you'll find that the chart updates automatically when you add new data to the worksheet.
To use this technique for your own data, make sure that the first argument for the OFFSET function refers to the first data point, and that the argument for COUNTA refers to the entire column of data. Also, if the columns used for the data contain any other entries, COUNTA will return an incorrect value.
2.Select Insert, Name, Define to bring up the Define Name dialog box.
3.In the 'Names in workbook' field, enter Date. In the 'Refers to' field, enter this formula:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1! $A:$A)-1)
4.Click Add to create the name. Notice that the OFFSET function refers to the first data point (cell A2) and uses the COUNTA function to get the number of data points in the column. Because column A has a heading in row 1, the formula subtracts 1 from the number.
5.Now type Sales in 'Names in workbook', and in 'Refers to' enter this formula:
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)
7.Click Add, and then OK to close the dialog box.
Activate the chart and select the data series. In this example, the (unmodified) formula in the formula bar will read:
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$10, Sheet1!$B$2:$B$10,1)
8.Replace the range references in the SERIES formula with the names you defined in steps 4 and 5. The SERIES formula should read:
=SERIES(,Sheet1!Date,Sheet1!Sales,1)
After performing these steps, you'll find that the chart updates automatically when you add new data to the worksheet.
To use this technique for your own data, make sure that the first argument for the OFFSET function refers to the first data point, and that the argument for COUNTA refers to the entire column of data. Also, if the columns used for the data contain any other entries, COUNTA will return an incorrect value.
