Excel tips and tutorials
Monday was Victoria Day in Canada, which is the unofficial start of summer. However, my tomato plants are huddled in the garage, wearing little polar fleece sweaters, hoping it will warm up soon.
It dipped to 1°C last night, but at least it didn’t snow!
Despite the frosty weather, I’m putting the Contextures Blog on summer hours, and will be posting Monday, Wednesday and Friday.
I hope it’s warmer where you are!
_____________
Roger Govier has created a sample workbook and instructions that show you how to create dynamic dependent data validation lists.
His technique lets you create multiple levels of dependent data validation, without defining a named range for each list.
Instead, Roger’s formula uses the INDEX function, with MATCH, to find a column heading, then shows the list from that column.
For example, on one sheet you can list regions, countries, areas and cities.
Then, on another sheet, select a region in column A, and see only the related countries in column B’s data validation drop down.
In column C, you’d see only the areas in the selected column, then only the applicable cities in column D.
With Roger’s technique, you’ll create four defined ranges, then use two of those ranges as the source for data validation drop down lists.
The ranges are dynamic, so you can add more items to any list, or add new lists, and the defined ranges will automatically adjust.
There are full instructions for Data Validation – Dynamic Dependent Lists, and a sample workbook that you can download.
Also, Roger Govier‘s contact information is at the bottom of that page.
____________________
If you’re eagerly awaiting the next release of Microsoft Office, you can find some early information at the new official Office 2010 blog.
There’s not much news there yet, but you can subscribe to the RSS feed and read any updates as they’re posted.
Also, there’s an Office 2010 web site, where you can apply to join the Technical Beta. I’ve signed up — did you?
Ask your questions in the Office forums on the Microsoft site.
For Excel related news, watch the Microsoft Excel blog. They did a great job leading up to Excel 2007, and I hope they’ll do the same for this version.
There are even a few screen shots in the Microsoft press gallery.
_______________
In Excel, you can create a drop down Data Validation list, so it’s easy to enter valid items in a cell.
Usually, the list of valid items is stored in the same workbook, but it’s possible to set up a drop down from a list in another workbook — as long as the other workbook is open.
Continue reading “Excel Drop Down List From Different Workbook”
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.
If you follow the steps below, you can use the Chart Wizard, instead of creating the default pivot chart.
Next, complete the remaining Chart Wizard steps, selecting the options you want
When you’re done, click the Finish button, to create the pivot chart.
If you select a pivot table as the data range, you can’t return to Step 2 of the Chart Wizard.
To use a different data range, you’ll have to start over, and create a new chart.
__________________
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. Here is a technique that lets you jump to the next data entry cell in Excel, without any macros.
Continue reading “Jump to the Next Data Entry Cell in Excel”
Recently, I wrote about creating dynamic charts in Excel, where you can select a date range from drop down lists. In that article I warned that the dynamic values would disappear if the entire range is selected, in Excel 2007.
I’ve done more testing, to see when the values stick, and when they disappear, and there’s a new version of the file for you.
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.
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.”
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 you have multiple value fields in the pivot table, select all the captions, and use the Replace All command to fix all the captions at once.
This will leave a space at the start of the caption, so it’s different from the original field name, and will be accepted.
____________________
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 Excel chart dates from a drop down list, and the chart changes to show the new range.
Continue reading “Select Excel Chart Dates From a Drop Down List”
