Calculating Easter

Category: Formulas | [Item URL]

Easter is one of the most difficult holidays to calculate. Several years ago, a Web site had a contest to see who could come up with the best formula to calculate the date of Easter for any year. Here's one of the formulas submitted (it assumes that cell A1 contains a year):

=DOLLAR(("4/"&A1)/7+MOD(19*MOD(A1,19)-7,30)*14%,)*7-6

Just for fun, I calculated the date of Easter for 300 years from 1900 through 2199. Then I created a pivot table, and grouped the dates by day. And then, a pivot chart:

During this 300-year period, the most common date for Easter is March 31 (it occurs 13 times on that data). The least common is March 24 (only one occurrence). I also learned that the next time Easter falls on April Fool's Day will be in 2018.

Converting Unix Timestamps

Category: Formulas | [Item URL]

If you import data you might encounter time values stored as Unix timestamps. Unix time is defined as the number of seconds since midnight (GMT time) on January 1, 1970 -- also known as the Unix epoch.

For example, here's the Unix timestamp for August 4, 2008 at 10:19:08 pm (GMT):

1217888348

To create an Excel formula to convert a Unix timestamp to a readable data and time, start by converting the seconds to days. This formula assumes that the Unix timestamp is in cell A1:

=(((A1/60)/60)/24)

Then, you need to add the result to the date value for January 1, 1970. The modified formula is:

=(((A1/60)/60)/24)+DATE(1970,1,1)

Finally, you need to adjust the formula for the GMT offset. For example, if you're in New York the GMT offset is -5. Therefore, the final formula is:

=(((A1/60)/60)/24)+DATE(1970,1,1)+(-5/24)

A simpler (but much less clear) formula that returns the same result is:

=(A1/86400)+25569+(-5/24)

Both of these formulas return a date/time serial number, so you need to apply a number format to make it readable as a date and time.

Excel 2007 Upgrade FAQ: Macros

Category: General VBA | [Item URL]

Note: I originally posted this information at Daily Dose of Excel. I've updated it slightly, and augmented it with information from commenters.

Q. How do I record a macro?

A. Click the little square icon in the bottom left of the status bar.

Q. How do I run a macro?

A. Choose Macros in the Code group of the Developer tab.

Q. I don't have a Developer tab.

A. Display the Excel Options dialog box, click Popular, and then enable 'Show Developer tab in the Ribbon.'

Q. I recorded a macro and saved my workbook. When I reopened it, the macros were gone!

A. By default, Excel proposes that you destroy your macros when you save the workbook. When you save the file, read the warning very carefully, and don't accept the default "Yes" button.

Q: I recorded a macro while formatting a chart, and the macro was empty.

A: The VBA macro recorder ignores formatting applied to individual chart elements. Maybe this will be fixed in a future service pack.

Q: Using VBA to modify Shapes is very tricky, so I tried to record a macro while working with a Shape. The macro was empty.

A: Maybe this will be fixed in a future service pack.

Q: I'm trying to automate creating a simple SmartArt diagram. Recording a macro produces an empty macro.

A: Maybe this will be fixed in a future service pack.

Q: How do I use VBA to add a simple button to the ribbon?

A: You can't. You must write XML code and insert the document into a workbook file using 3rd party tools. Or, if you're a glutton for punishment, you can do it by unzipping the document and making the edits manually.

Q: How do I use VBA to activate a particular tab.

A: Sendkeys is your only choice. Press the Alt key to find out the keysroke(s) required. For example, to switch to the Page Layout tab, use this: Application.SendKeys "%w{F6}"

Q: I'm trying to display a topic from a *.chm help file from a Messge Box or an Input Box. Using Application.Help simply displays the main Excel help window.

A: Maybe this will be fixed in a future service pack.

Q: Can I use the VBA Application.Help method to display a particular Excel 2007 help topic?

A: No, but you can use Application.Assistance.ShowHelp method. First, navigate the local Help system and identify the topic ID. Right-click, and select the "Copy xxxxxxxx" option (this copies the topic ID to the clipboard. Then use a VBA statement like this: Application.Assistance.ShowHelp "HP10062493". The text in quotes is the topic ID pasted from the clipboard.

Excel 2007 Upgrade FAQ: Charts And Graphics

Category: General / Charts & Graphics | [Item URL]

Note: I originally posted this information at Daily Dose of Excel. I've updated it slightly, and augmented it with information from commenters.

Q: Double-clicking on a chart element doesn't display the Format dialog box.

A: Yes, that handy mouse action no longer works. Right-click a chart element, and choose Format xxxxx from the shortcut menu. Or, press Ctrl+1.

Q. I find that it's very difficult to select some of the elements on a chart by clicking. Is there any easier way to select a particular chart element?

A. Use the arrow keys to cycle among the elements on a chart. Or, use the Chart Elements drop-down control in the Chart Tools / Layout / Current Selection group. Better yet, add the Chart Elements control to your QAT so it's always visible.

Q: How do I prevent a chart from changing its size when I resize the underlying rows or columns?

A: Select the chart, then click the dialog box launcher in the Chart Tools / Format / Size group to display the Size And Properties dialog box. Use the controls in the Properties tab to change the move and size properties.

Q: What's a dialog box launcher?

A: It's the tiny icon in some of the ribbon groups. The icon is displayed on the right side of the group name.

Q: I'm working with a chart, using the modeless Format dialog box. If I click in a cell, the Format dialog box inexplicably displays the title 'Format Shape,' and it has the focus. So the arrow keys move within the dialog box, not the worksheet.

A: Annoying, isn't it? When you're finished working with the Format dialog box, press Escape to close it and return the focus to your worksheet. Maybe this will be fixed in a future service pack.

Q: In a chart, how do I control plotting empty cells and plotting hidden cells?

A: Select the chart, then choose Chart Tools / Design / Data / Select Data. In the dialog box, click the button labeled Hidden and Empty Cells. 

Q. What happened to the chart fill patterns?

A. The fill patterns are no longer available in the user interface. However, they can be applied using a VBA macro.

Q: In previous versions, I could use the Increase and Decrease Decimal buttons to change the number of decimal places displayed in a chart trendline equation. Those buttons don't work in Excel 2007.

A: Click the trendline equation box and press Ctrl+1 to display the Format Trendline Label dialog box. Click the Number tab, select the Number category, and set the number of decimal places.

Q. How do I change the shape of a cell comment? In Excel 2003 I used Change Autoshape on the Drawing toolbar.

A. Right-click your QAT and choose Customize. Choose 'All Commands' and then select 'Change Shape'. Click Add to add the command to your QAT. Then you can use it to change the shape of a comment.

Q. A cell comment is a shape. Why can't I use the ribbon commands to format it?

A. Comment formatting is done via the Format Comment dialog box. Right-click the comment's border and choose Format Comment. Oddly, the color options available are not from the document theme.

Q: I can change the Height and Width of an object by entering values in the controls in the Format / Size group. How do I enter values for the Top and Left properties?

A: You don't. You can, however specify the Top and Left properties by using VBA.

Q: When I right-click a Shape, I see an option to "Set as Default Shape." This command seems to have no effect.

A: When you choose that command, Excel uses the *formatting* that you've applied to the shape as the default (fill, outline, effects). The command should probably read "Set as Default Shape Formatting."

Excel 2007 Upgrade FAQ: Formatting And Printing

Category: General / Formatting / Printing | [Item URL]

Note: I originally posted this information at Daily Dose of Excel. I've updated it slightly, and augmented it with information from commenters.

Q: How do I get my old workbook to use the new fonts?

A: Press Ctrl+N to create a blank workbook. Activate your old workbook and choose the Home tab. Click the very bottom of the vertical scrollbar in Styles gallery, and choose Merge Styles. In the Merge Styles dialog box double-click the new workbook you created with Ctrl+N. But this only works with cells that have not been formatted. For example, bold cells retain their old font.

Q. How do I get a print preview?

A. Try using the Page Layout view (icon on the right side of the status bar). Or, add the Print Preview button to your QAT.

Q: When I switch to a new document template, my worksheet no longer fits on a single page.

A: That's probably because the new theme uses different fonts. After applying the theme, use the Page Layout / Themes / Fonts control to select your original fonts to use with the new theme. Or, modify the font size for the Normal style. If page fitting is critical, you should choose the theme before you do much work on the document.

Q: How do I get rid of the annoying dotted-line page break display in Normal view mode?

A: Open the Excel Options dialog box, click the Advanced tab, scroll down and look for the 'Display options for this worksheet' section, and remove the checkmark from 'Show Page Breaks'.

Q: Can I add that 'Show Page Breaks' option to my QAT?

A: No. For some reason, this very useful command isn't available as a QAT icon.

Q: I changed the text in a cell to use Angle Clockwise orientation (in the Home / Alignment group). I can't find a way to get the orientation back to normal. There's no Horizontal Alignment option.

A: To change the cell back to normal, click the option that corresponds to the current orientation (that option is highlighted). Or, choose the Format Cell Alignment option and make the change in the Format Cells dialog box.

Q. I'm trying to apply a table style to a table, but it has no effect.

That's probably because the table cells were formatted manually. Remove the old cell background colors, and applying a style should work.

Q: I thought Office 2007 was supposed to support PDF output. I can't find the command.

A: You need to download a free add-in from Microsoft. Blame the Adobe attorneys. After you download and install the add-in, click the Office Menu button and then select Save As / PDF or XPS.

Excel 2007 Upgrade FAQ: General

Category: General | [Item URL]

Note: I originally posted this information at Daily Dose of Excel. I've updated it slightly, and augmented it with information from commenters.

General

Q: I opened a workbook and my worksheets have only 65,536 rows.

A: Save it in an Excel 2007 format, close it, then re-open it.

Q: Where is the list with open workbooks?

A: Use View / Window / Switch Windows. Better yet, add this command it to your QAT. Right click on Switch Windows to add it so it is always one click to access it.

Q: Ctrl+A doesn't select all of the cells in my worksheet.

That's probably because the cell pointer is inside of a table. Press Ctrl+A three times to select all worksheet cells.

Q. The Custom Views command is grayed out.

A. That's probably because your workbook contains a table. Convert the table to a range, and then you can use Custom Views.

Q: What happened to the ability to create a pivot table using the Multiple Consolidation Ranges option?

A: That option still exists, but you need to add the 'PivotTable and PivotChart Wizard' command to the Quick Access toolbar (Found in 'Commands not in the Ribbon'), and use that command to start a new pivot table.

Q: I can't find the command to apply names to cell references in a formula. In Excel 2003, the command was Insert / Name / Apply.

A: The Define Name control in the Formulas / Defined Names groups is a drop-down. Click the down-arrow, and you'll see the Apply Names command.

Q: Why doesn't the F4 function key repeat all of my operations? 

A: I don't know. The very useful F4 is much less useful in Excel 2007.

Q. What happened to the ability to "speak" the cell contents?

A. To use those commands, you must customize your QAT. They are listed under 'Commands Not in the Ribbon'.

Q: Where is the Mail Recipient (body) option in Excel 2007?

A: You must customize your QAT. They are listed under 'Commands Not in the Ribbon'

Excel 2007 Upgrade FAQ: User Interface

Category: General | [Item URL]

Note: I originally posted this information at Daily Dose of Excel. I've updated it slightly, and augmented it with information from commenters.

Q: How do I open a file?

A: That round logo in the upper left corner is not just for cosmetics. It's called the Office Menu button, and it's used for a variety of purposes, including file operations. Click it.

Q. How do I get to the Excel Options dialog box?

A. Click the round Office Menu button, then click Excel Options.

Q. I clicked the round Office Menu button, but I don't see Excel Options.

A. Look at the very bottom. It's a button, not a menu item. And make sure you don't click Exit Excel by mistake.

Q: how do I hide/show the Ribbon?

A: Use Ctrl-F1 to toggle the display of the ribbon.

Q: Where did the xxxxxx command go?

A: There's a pretty good chance that it's on the ribbon somewhere. But there's also a chance that it's not on the ribbon. In the latter case, you can add the command to your QAT. Also, try using the old Excel 2003 hot keys (for example, Alt+T, I to display the Add-Ins dialog box).

Q. What's a QAT?

A. QAT is Quick Access Toolbar. This is the only user interface element that can be customized by the end user.

Q. Where are my old custom toolbars?

A. Click the Add-Ins tab and you'll see them.

Q. I can't make my old custom toolbars float.

A. No, you can't.

Q. How do I "tear off" the Fill Color icon so I can float it?

A. You can't.

Q. How do I get Help? The 'ask a question' box is gone.

A. Press F1, or click the little question mark icon in the title bar.

Q: How can I hide the status bar in Excel 2007?

A: You must use VBA to hide the status bar. Either of these statements does the job:

Application.DisplayStatusBar = False

Application.CommandBars("Status Bar").Visible = true

Q: How do I get back to the classic PivotTable wizard?

A: Press Alt+D+P

Q: Why can’t I drag fields in my PivotTable any more?

A: You can. Right click on the pivot table in question, and select PivotTable Options. Next, select the Display tab, and then place a check next to "Classic PivotTable Layout"

Pasting An Image To A UserForm Control

Category: Charts & Graphics / UserForms | [Item URL]

VBA programmers are familiar with Excel's UserForms. The UserForm object, as well as many of the ActiveX controls that are placed on a UserForm, can display an image -- the object's Picture property.

How to set the Picture property

If you believe Excel's online help, you'll think that there are two ways to set the Picture property for an object:

• At design time: Browse for an image file, by clicking the "..." button in the Properties box
• At run-time: Use VBA's LoadPicture function to retrieve an image file.

But there's another way. And it doesn't even require that the image is stored in a file. Here's the trick:

1. Locate the image that you want to use, and copy it to the Clipboard.
2. Activate the VB Editor and select the object that will contain the image (i.e., a UserForm, or a control that has a Picture property).
3. Make sure the Properties window is displayed (press F4 it it's not).
4. Click the Picture item. By default, this displays "(None)" as in the figure above.
5. Press Ctrl+V to paste the image on the Clipboard to the control.

More

Importantly, the picture that you paste to a control need not be stored in a file. Any picture that can be copied to the Clipboard can be pasted to an object's Picture property. And this includes charts stored in a worksheet. The trick here is to copy the chart as a picture: Click the chart, press Shift, and choose Copy Picture from Excel's Edit menu. Once copied, you can paste the picture of the chart to your UserForm control.

When pasting a chart, it's important to understand that the picture is not linked to the original chart. So if the chart changes, the picture will not change.

Interactive Chart With No Macros

Category: Charts & Graphics | [Item URL]

A companion file is available: Click here to download

This tip presents a highly interactive workbook, without a single macro. The workbook allows you to select two U.S. cities, and then displays a comparison chart for the following variables.

• Temperature
• Precipitation
• Percent Sunshine
• Wind

You can download the workbook and examine how it's done. Hint: It's done with named ranges and worksheet controls.

Using Custom Number Formats

Category: General | [Item URL]

A companion file is available: Click here to download

One of the most useful (and underutilized) features in Excel is the ability to create custom number formats. Although Excel offers a wide variety of standard number formats, it's often advantageous to develop custom formats. For example, if you use large values, you can scale the display of those values so they appear "in thousands." You can make 123,456,789 appear as 123,457 by applying this format:

  #,##0,

A number format consists of three parts: code for negative, code for zero, and code for positive values. The code for each part is separated with a semicolon. If you would like a cell to appear empty, use this format (which is three semicolons and nothing else):

;;;

To apply a custom number format:

1. Select the cell or range that you want to format
2. Choose the Format Cells command (or press Ctrl+1)
3. Click the Number tab on the Format Cells dialog box
4. Click the Custom category
5. Enter the number format code into the edit box labeled Type.

Rather than provide a complete tutorial on custom number formats, I refer you to Excel's help. You'll find a comprehensive reference for the formatting codes.

NOTE: It's important to understand that a number format affects only the way in which the number appears. A number format does not change the underlying value in the cell.

Page 3 of 19 pages
[Previous page]   [Next page]