Create Excel Pivot Chart With Chart Wizard

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 the steps below, 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 the Data range box.
entire pivot table is automatically selected
entire pivot table is automatically selected

Complete Chart Wizard Steps

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.

Chart Wizard Note

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.
__________________

Jump to the Next Data Entry Cell in Excel

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”

Problems With Dynamic Charts 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.

Continue reading “Problems With Dynamic Charts in Excel”

Rename Excel Pivot Table Value Field

When you add a field to the pivot table Values area, it’s automatically given a custom name, such as Sum of Units.

Default pivot table value field names
Default pivot table value field names

Use a Shorter Name

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.

PivotUnitsChange

Use Replace All

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.

  1. In the Find What box, type “Sum of” (do NOTadd a space at the end)
  2. Leave the Replace With box empty
  3. Click Replace All.

This will leave a space at the start of the caption, so it’s different from the original field name, and will be accepted.

Find and Replace dialog box
Find and Replace dialog box

____________________

Select Excel Chart Dates From a Drop Down List

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”

Automatically Update Access Front End Files

When sharing an Access database, you should have the data in one file (the back end) and the queries, reports, forms and modules in a separate database (the front end).

Give each user their own copy of the front end, linked to the shared back end.

Keep a master copy of the front end file, tucked away in a safe place. If you need to update the front end, such as adding new features to a form, make the changes in the master copy of the front end file (make a backup copy first!)

To distribute the revised front end to all users, you can use a utility like Tony Toews’ Auto FE Updater. I use this utility for most of the databases that I’ve installed at client sites, and it works beautifully.

[Update: This utility no longer has a free version]

The utility adds a shortcut to the user’s desktop, which they double-click to open the database. If the master copy of the front end has been updated, the utility downloads a copy of the revised version.

Remember to Compile

The only problem that I’ve had was with one user, who has Access 2000 installed. I’m using Access 2003 to update the master file, and all the other users have Access 2002 or 2003 installed.

Sometimes, after I’d updated the master file, that user couldn’t open the new version. Error messages would appear, and the VBA code looked like pieces were missing. Very strange.

When I made the latest change to the database, I compiled the code before closing the file.

DebugCompile

It dawned on me that maybe I’d occasionally forgotten to do this, and that could be causing the problem.

I checked the previous version, and it wasn’t compiled. When I uploaded the new compiled version, that user was able to open it with no problem.

That’s not definitive proof that compiling will prevent future problems, but I’m keeping my fingers crossed.
____________________

Make Excel Numbers Line Up Correctly When Zoomed

At 100% zoom setting in Excel, the numbers display correctly. If I have a column of 7 digit numbers, all 7 numbers line up correctly, all down the column.

For example, in the screen shot below, 1111111 is the same width as 8888888.

NumPropor01

Problem When Zoomed

However, if I zoom to something less than 80%, the numbers become proportional, and don’t line up correctly. Here are the same numbers and dates, at 70% zoom.

NumPropor02

An entry of 1,111,111 is much narrower than 8,888,888.

This can cause problems, if you’re skimming down a long column of numbers, to spot entries that are unusually large.

[Update] There is a newer version of this article here: Keep Numbers Aligned When Zooming

Change a Registry Setting

You can change a registry setting, as described in the following MSKB article, to prevent numbers from displaying in proportional font.

  • WARNING: As always, you should make a backup copy of the registry before you tweak any settings:

Here is the article link:

Euro Currency Character Is Not Displayed Correctly in Excel 2003

This is really a fix for a Euro symbol display problem, but it also fixes the proportional font display.

Steps Suggested by Microsoft

Here’s what the article suggests, and you can replace the 11.0 with the version of Excel that you’re using:

  • Quit any programs that are running.
  • Click Start, and then click Run.
  • In the Open box, type regedit, and then click OK.
  • Locate, and then click to select the following registry key:
    HKEY_CURRENT_USERSoftwareMicrosoftOffice11.0ExcelOptions
  • After you select the key that is specified in step 4, point to New on the Edit menu, and then click DWORD.

RegistryDWord

  • Type FontSub, and then press ENTER.
  • Right-click FontSub, and then click Modify.
  • In the Value data box, type 0, and then click OK.

RegistryDWordEdit

  • On the File menu, click Exit to quit Registry Editor.
  • Start Excel.

Test the Result

After making the registry change, open Excel and zoom to 75% or lower. The numbers should remain displayed in non-proportional font.

Here are those numbers at 70%, after the fix – problem solved!

NumPropor03
_________________

Excel Keyboard Shortcuts for Ribbon

While trying to type a hyphen in Excel 2007, I stumbled onto a handy keyboard shortcut.

I had a bit of an overreach on the hyphen, and hit the F10 key instead. To my amazement, the Ribbon filled with little tags.

RibbonTags

Maybe I’ve been living under a rock, but I don’t remember seeing those tiny tags before. Have you noticed them?

There’s a number or letter on each Ribbon tab, like H for Home, N for the Insert tab, and R for the Review tab.

Above that, there is a tag for each icon on the Quick Access Toolbar (QAT) too! They are numbered from 1 to 9, and then a set of descending numbers, from 09 down to 01.

Use the Shortcut keys

Here’s how you can use the shortcut keys that those tags represent:

  • First, press the F10 key, to activate the Ribbon.
  • Next, type the letter(s) or number(s) that you see on any Ribbon tab or on any of the QAT commands

See Data Tab Commands

For example, I can type an A to activate the Data tab. (Note: Even if the Data tab is active, you’ll have to type an A to see its command tags.)

Tags will appear on the available Data commands.

RibbonTagsData

Because the active cell is in a named Excel table on my worksheet, most of the Data tab commands are available.

  • For example, I can type the letter T, to add an AutoFilter
  • Or, I could type the letter Q, to start an Advanced Filter.

Useful Feature

It seems like a useful feature, and the only shortcut that I have to memorize is the F10 key.

Anyway, it’s nice to know that a typo can lead to an interesting discovery.
_____________________

Turn Off Excel Paste Options Popup

It’s nice to have a new laptop, but it’s amazing how many options and settings need to be adjusted.

For example, on my other machines I’ve changed the settings so those annoying Paste Options and AutoFill Options buttons don’t appear.

Now it’s time to fix them on the new laptop. Does anyone actually use those buttons?

Video: Turn Off Paste Options

Here’s a video I created while changing the Paste/AutoFill options.

Note: I’m also testing the latest version of Camtasia Studio, and it has some video recording settings that I need to adjust too!

To turn off the buttons in Excel 2007:

  1. Click the Office Button, then click Excel Options
  2. Click Advanced, and in the Cut, Copy and Paste section, remove the check marks from ‘Show Paste Options buttons’ and ‘Show Insert Options buttons’.
  3. Click OK
Cut, Copy and Paste section
Cut, Copy and Paste section

To turn off the buttons in Excel 2003 and earlier:

  1. On the Tools menu, click Options
  2. On the Edit tab, remove the check marks from ‘Show Paste Options buttons’ and ‘Show Insert Options buttons’.
  3. Click OK

_________________