|
|
If you select a cell in a pivot table and click the Chart Wizard button on the toolbar, a default pivot chart is created, on a new chart sheet. Sometimes you might want to step through the Chart Wizard, so you can set the options as you create the pivot chart. Use the Chart Wizard If you follow these steps, you can use the Chart Wizard, instead of creating the default pivot chart. Select an empty cell on the worksheet, away from the pivot table and any other data. On the Excel toolbar, click the Chart Wizard button. In Step 1 of the Chart Wizard, select a Chart type and Chart sub-type, then click Next. In Step 2 of the Chart Wizard, click in the Data range box, and select any cell in the pivot table. The entire pivot table will be automatically selected, and its address will appear in
Continue reading Create a Pivot Chart With the Chart Wizard
If you're filling in a form, the data entry cells might be scattered throughout the worksheet. You'd like a quick and easy way to move through the cells, in a specific order. To do this, you can create a named range.
Select the Data Entry Cells
You'll select the data entry cells, in the order you want to use them. In this report card, the cells are numbered, to show the data entry order.
First, select the second cell in which you want to enter data. In this example, we'll select cell C4, where the student's grade level will be entered. Then, hold the Ctrl key and select the next cell, then the next, until the remaining cells, 3 to 7, are selected. Finally, select the first cell in the sequence – cell C3 in this example.
Because cell C3 was selected last, it becomes the active cell in
Continue reading Jump to the Next Data Entry Cell in Excel
Moved to the Excel Twitters archive: Excel Twitters 20090508
A couple of days ago, I wrote about creating dynamic charts in Excel, and allowing users to select a date range from drop down lists. At the end of the article I warned that the dynamic values would disappear if the entire range is selected, in Excel 2007.
Since then, I've done some testing, to see when the values stick, and when they disappear.
Excel 2007 Tests
First, I tested Excel 2007, with and without an Excel Table, and using different formulas for the named ChartDates range.
Table Dyn Lost ChartDates Range Yes Yes =Table1[Date] Yes Yes =OFFSET(Chart!$A$1,1,0,COUNT(Chart!$A:$A),1) Yes Yes =Chart!$A$2:$A$23 No No =OFFSET(Chart!$A$1,1,0,COUNT(Chart!$A:$A),1) No No =Chart!$A$2:$A$23
With the data in an Excel Table, the dynamic chart values were always lost, if the full date range was selected. So, the best option in Excel 2007 is to avoid an Excel Table, and use a dynamic ChartDates range.
Excel 2003 Tests
Continue reading Problems With Dynamic Charts in Excel
If you have a folder full of MP3 files, you can use Excel to create a list of the files, including title, artist, and song duration. I pulled together a collection of music for a family wedding, and wanted to share the list with the bride and groom. Rather that write some code myself, I checked a few of my favourite Excel sites. John Walkenbach has a download that looked perfect for the job. I downloaded his MP3 File Lister, opened the file, and clicked the Start button. I was prompted to select a folder, and a few seconds later, the list was finished. It had included all the files from the top level folder, and all the subfolders. The Artist and Genre columns were switched, as were the Album and Duration columns. The code is unlocked though, so you could easily change the headings if you encounter
Continue reading Excel List of All MP3 Files in a Directory
When you add a field to the pivot table Values area, it's automatically given a custom name, such as Sum of Units. You might want to change the custom name to Units, so it's easier to read and makes the column narrower. However, if you select the cell and type Units, you'll get an error message: "PivotTable field name already exists." Use a Slightly Different Name If you try to create a custom name that's the same as a field name in the source data, you'll see the error message. In this example, because one of the fields in the source data is named Units, you can't use Units as a custom name in the pivot table. However, you can avoid this problem, by adding a space character to the end of the custom name, and it will be accepted. Or, if
Continue reading Renaming a Pivot Table Value Field
Instead of showing all the data in a chart, you can select a specific date range, and show only the data from that period. In this example, drop down lists of dates are created with data validation. Select a start and end date, and the chart changes to show the new range.
To create this dynamic chart in Excel 2007, you can create a table from the chart data, then create named ranges, and use those names in the chart.
Create an Excel Table for the Chart Data
[Note: Please see the post, Problems With Dynamic Charts in Excel and ignore this step -- the Excel Table may cause problems.]
On the Chart sheet, select a cell in the chart data, e.g. cell A1 On the Ribbon, click the Insert tab, then click Table In the Create Table dialog box, click OK, to create the table for the selected
Continue reading Select Excel Chart Dates From a Drop Down List
|
|
Recent Comments