Uh-oh! It's almost Christmas and you haven't mailed any greeting cards yet. Don't worry, you can create a personalized Excel message box instead! That will warm your co-workers' hearts, and it saves paper and postage costs too.
Get the Application UserName
In Excel VBA, you can use Application.UserName to get the registration name for the Microsoft Office applications.
However, some people don't update that setting, and it might show a company name, instead of the user's name.
Get the Network UserName
Another option is to get the network user name, with a Windows API call. There is sample code in the Microsoft Knowledgebase: Visual Basic Procedure to Get Current User Name, and I've used a variation on that code in my sample file.
In the sample code shown below, the network username is pulled from the DLL. This code is stored in a regular code module.
'==================================
' Access the GetUserNameA function _
' in advapi32.dll and
' call the function GetUserName.
Declare Function GetUserName _
Lib "advapi32.dll" _
Alias "GetUserNameA" _
(ByVal lpBuffer As String, _
nSize As Long) As Long
'==================================
' Main routine to Dimension variables,
' retrieve user name
' and display answer.
Function Get_User_Name() As String
' Dimension variables
Dim lpBuff As String * 25
Dim ret As Long, UserName As String
' Get the user name minus any
' trailing spaces found in the name.
ret = GetUserName(lpBuff, 25)
Get_User_Name = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
End Function
'==================================
Create the Personalized Excel Message
Next, you can create an Excel message box, and show the network username in the message text. In this example, I've also calculated the year for the current date. Then, add 1 to the year number, to offer best wishes for the upcoming year.
'==================================
Sub ChristmasMessage()
' show greeting with username and
' upcoming year
MsgBox " Merry Christmas, " _
& Get_User_Name & " " _
& vbCrLf _
& " and best wishes for " _
& Year(Date) + 1 & "! "
End Sub
'==================================
Download the Sample File
You can visit the Contextures website, to download the personalized Excel Message Box sample file. The file is in Excel 2007/2010 format, and is zipped.
In the sample workbook, the Workbook_Open event runs this macro, so the personalized message appears when the file is opened, if macros are enabled.
Watch the Video
To see the steps for adding the GetUserName code to your workbook, and creating a personalized Excel message, you can watch this short Excel video tutorial.
I'm just getting back from a vacation in South Carolina, where I didn't use Excel too often, except to add up all my Christmas shopping expenses. If you're on my shopping list, you might be getting a basket from the Charleston market.
Or maybe you'd like some of the local barbeque sauce or a bag of grits?
Christmas Planner
There are only 10 days until Christmas, and I'm almost ready. How about you? If you're still planning and shopping, there is an Excel Christmas planner on the Contextures website, that you can use to help you stay organized.
The Excel Christmas planner has a budget sheet, where you can plan and track your spending.
Christmas Gift List
If you're lucky, you don't have too many gifts to buy. But even for a few gifts, it helps to make a list to keep track of costs. The Excel Christmas planner helps you track your gifts too.
Christmas Tasks
The Excel Christmas planner has other sheets too, including a Christmas task list, so you can keep track of all those important things you're supposed to do over the next few days. You don't want to realize on December 24th that no one has ordered the turkey!
Winter Weather
It was nice to spend some time in the warmer weather, and yes, it's colder back here in Canada, but at least we don't have to watch for alligators! Now I'd better go and order that turkey.
If other people will be using the Excel files that you build, it might help them if you add some instructions in a Text Box. After you add the text, drag the handles to adjust the Text Box size, so all the instructions are visible.
If you plan to edit the text occasionally, or change the text formatting, you can set the Text Box to resize automatically.
Right-click on the Text Box, and click Format Shape,
Then, click the Text Box category, and add a check mark to Resize Shape to Fit Text.
In Excel 2007, the Text Box loses its Wrap Text setting, if you turn on the auto resize. Instead of a narrow text box, you'll often end up with a really wide one.
Tip: Add a few manual line breaks, if you want the Text Box to be narrower.
Excel 2010 Auto Resize With Wrap Text
Thanks to Bob Ryan, of Simply Learning Excel, who let me know that the Text Box resize feature has improved in Excel 2010.
Now, when you open the Format Shape window, there is also a Wrap Text in Shape checkbox.
After you change the Resize setting, the Text Box will get taller, to fit all the text, instead of getting wider.
Adjust the Text Box
With the Auto Resize setting turned on, you can drag the side or corner handles to change the size of the text box. The text box will adjust to the width that you have set, and automatically changes the height, so all the text is visible.
Warning: If you resize the text box with the top or bottom handle, the Resize to Fit setting is automatically turned off.
Text Box Resize Macro
If you're using Excel 2010, and have lots of text boxes in a workbook, you can use a macro to change the Resize to Fit setting.
You can see the Text Resize code, and download the sample workbook, on the Contextures website: Excel TextBox Formatting
Watch the Text Box Resize Video
To see the steps for inserting a text box, and formatting it in Excel 2007 and Excel 2010, you can watch this short Excel video tutorial.
Do you ever right-click on something in Excel, and the command that you wanted to use isn't on that pop-up menu? For example, if right-click on a cell, there is no command to turn off the gridlines.
If you can't find the command on the right-click menu, you have to go to the Excel Ribbon or Quick Access Toolbar (QAT) instead, and try to find the command on one of the tabs there.
What commands would you add to a right-click menu? Are there commands that you added to the QAT, that would be even better in a popup menu?
Customize the Right-Click Menus
Fortunately, Doug Glancy has created a solution to the right-click menu problem, with his MenuRighter Add-in. With Doug's free add-in, you can add commands to the pop-up menus, so the items that you need are easy to find.
The add-in is not for sissies! You need to know where the commands were on the old Excel 2003 menus – or be willing to poke around and find them.
Then, you select the right-click menu where you want to add the command, and click the buttons to add the command, and save the changes.
In the screen shot below, I have selected the ToggleGrid command from the Forms toolbar, and am adding it to the Cell popup menu, just above the Hyperlink command.
Find the Right Right Menu
There is a large collection of Targets, so it can be tricky to select the correct one. There are two Cell targets, so how can you decide which right menu is the right one?
While the MenuRighter is open, you can check the box for Show Labels on Menus. At the bottom of the list at the right, you can see the identity for the selected Cell target – 28-Cell.
If I right-click on cell A2, the popup menu also shows 28-Cell, so that confirms that I selected the Cell target that I want.
Use the Modified Right-Click Menus
After I added the Toggle Grid command to the Cell menu, I clicked Apply Changes, and closed the MenuRighter window.
Then, I can right-click on a cell, and turn the gridlines on or off.
It's a command that I use frequently, so it's very convenient to have it in the right-click menu. Thanks Doug, you made life in Excel a little easier!
Download the MenuRighter Add-in
For more instructions, and to download the MenuRighter add-in, you can visit Doug Glancy's website: MenuRighter Add-in.
Watch the MenuRighter Video
To see the steps for adding a command to a right-click menu, by using the free MenuRighter add-in, please watch this short Excel video tutorial.
Welcome back! The Contextures Blog was out of commission for a couple of weeks, and it's nice to be up and running again. A few of the shingles blew off during the reconstruction, so if you notice anything missing or broken, please let me know!
Focus on Data with Report Filter Fields
Now that we're back in business, let's take a look at the Report Filter fields in a pivot table. In older versions of Excel, these were called Page Fields, and they help you focus on specific data in an Excel pivot table.
Drop fields into the Report Filter area of the pivot table layout. Then, select one or more items from that pivot table field, to see the summarized data for the selected items.
In this example, the Order Date field is in the Report Filter area, and you can select a specific date, to see its orders.
Group Dates in the Date Field
If you put a date field in the Report Filter area, there might be a long list of dates in the dropdown list. Instead of seeing the individual dates, you might prefer to group them, by year or month.
However, if you right-click on the Report Filter field, there isn't a command that lets you group the data. Are you doomed to a miserable existence of scrolling through the date list?
The Date Grouping Workaround
Fortunately, there is a workaround that solves the Report Filter grouping problem. It's not pretty, but it works!
To enable the grouping command, you'll temporarily move the Report Filter field to the Row Labels area. In the screen shot below, the OrderDate field is being dragged to the Row Labels area.
Then, right-click on the field in the pivot table, and click Group. Select the Grouping options that you want, and click OK.
Back to the Report Filters Area
Move the grouped fields back to the Report Filter area. In this example, the OrderDate field was grouped by Year and Month, and that created a new field – Years.
Both the Years field and the OrderDate field are dragged back to the Report Filter area.
Now, the pivot table can be filtered by year and/or month.
Maybe in the next version of Excel you'll be able to group the fields, without moving them from the Report Filter area.
You're admired by your co-workers, thanks to your awesome Excel skills. Cupcakes magically appear on your desk, in thanks for your help with complex formulas. Your boss keeps stretching the budget, to accommodate the huge bonuses you get, in reward for your amazing talents. Well, that might not be the exact situation, but I'm sure your skills are appreciated!
Then, one day, it all goes horribly wrong. Your boss needs a report in 15 minutes, and you can't get a seemingly simple VLOOKUP formula to work. You can see the target product numbers in the lookup table, but the formula result is #N/A, instead of the product price.
You don't want to lose your Excel Expert badge over something this trivial, so how will you solve the problem?
Text or Number?
A common cause for this VLOOKUP error is that one of the values is a number, and the other is text. In this example, the lookup table codes in cell B2:B5 are stored as text values – they have a leading apostrophe. The lookup code, in cell B8, is entered as a number – no leading apostrophe.
So, it looks like cells B2 and B8 are equal, but Excel sees them as different values.
Fix the Text Values
The easiest solution to the the VLOOKUP problem in this example is to convert the text values to numbers, so the codes in the table match your lookup values.
Or, type an apostrophe in front of your lookup code in cell B8, so it's a text value too.
Change the VLOOKUP Formula
If you can't fix the data, you can convert the lookup value in the Excel VLOOKUP formula. Here is the original VLOOKUP formula, that returned an #N/A error.
=VLOOKUP(B8,$B$2:$D$5,2,FALSE)
If you add an empty string to the end of the value in cell B8, the lookup number will be converted to a text string. The revised formula is:
=VLOOKUP(B8 & "",$B$2:$D$5,2,FALSE)
This formula will also work if cell B8 contains a text value – adding the empty string won't change the value.
How can you quickly move around an Excel worksheet? That's what Andrea asked in a recent comment:
How do I "go to" a specific page of my worksheet. I have 23 pages and do not want to keep scrolling. I cannot find how to do this as on Word.
Of course, Andrea is correct, and there is no built-in page navigation for Excel, like Word has. What would you recommend for Andrea? Here are a couple of my favourite techniques, and please add your suggestions in the comments.
Navigating in Microsoft Word
In Word, you can click the buttons at the bottom right, to quickly go to the next or or previous page.
You can also double-click on the page count section of the status bar, type a page number, and press Enter, to go to a specific page.
Worksheet Pages in Excel
In Excel, we don't have those handy page navigation features. You can scroll down through the rows, with no hint as to what page you're on, if the worksheet has multiple printed pages.
Or, add the page number to the header or footer, and scroll in Page Layout view, where you can see those numbers.
Create a Table of Contents
If you'd like a structured way to move around the worksheet, you can create a worksheet table of contents. With this technique, you add section headings, with hyperlinks to those headings, at the top of the worksheet.
Then, click a hyperlink, to go to that section.
Create Named Ranges
For a quicker and easier method, you can skip the hyperlinks, and simply go with named ranges. If you're the only person using the workbook, you could use this simple navigation system.
In a long worksheet, you would most likely have heading cells, which you can name. In this screenshot, cell B9 is named as Income.
Then, go to any named range, by selecting its name from the Name Box drop down list.
Other Ways to Navigate
Excel isn't set up to deal with page navigation, but one of these suggestions might help Andrea work around this limitation. Do you have other techniques that you use in large worksheets?
This week, I was updating the GetPivotData Function page on my website, and remembered how hard it was to turn off that feature, in Excel 2003 and earlier. We won't even talk about the really olden days (Excel 2000), when you had to type those tricky GetPivotData formulas yourself!
Automatic Formulas
If you try to reference a pivot table cell, a GetPivotData formula may be automatically created, instead of a simple cell reference. This is thanks to the Generate GetPivotData feature, which is turned on by default.
The automatic formula can be a helpful feature, but sometimes you'd rather just have the cell link. You could type the link yourself, or find a way to turn off the formula feature.
GetPivotData in Excel 2003 and Earlier
In the old versions of Excel, if you want to stop that automatic formula creation, you have to add the Generate GetPivotData button to the PivotTable toolbar. If you're nostalgic for the old method, you can see it in the video at the end of this blog.
GetPivotData in Excel 2007 and Excel 2010
Now, it's much easier to turn the Generate GetPivotData feature on and off.
Select any cell in a pivot table.
On the Excel Ribbon, under PivotTable Tools, click the Options tab.
In the PivotTable group, click the drop down arrow for Options
Click the Generate GetPivotData command, to turn the feature on or off.
GetPivotData Formulas
There is more information on the GetPivotData Function page, including examples of using cell references within the formula. It's a great way to pull specific data from your pivot tables.
Generate GetPivotData Button in Excel 2003
To see how we changed this setting in the olden days, you can watch this short video.
Now, it's getting close to Thanksgiving in the USA, so lets see how to calculate that date, with an Excel worksheet formula.
Date Range for Thanksgiving
First, you can use simple arithmetic to figure out the possible date range for the US Thanksgiving, which falls on the fourth Thursday in November.
The first Thursday could occur anytime during the first 7 days of November, and if we add 21 days to that date range, the fourth Thursday falls between November 22nd and November 28th each year.
Weekday Numbers
As part of the formula to calculate the Thanksgiving date, we'll use the WEEKDAY function, which assigns a number to each day of the week. The default setting is to start with Sunday, as weekday number 1. In the table below, you can see that Thursday is weekday number 5.
Thanksgiving Formula
The Thanksgiving formula that I'm using was posted by Daniel.M in the old Excel newsgroups. The formula starts with the DATE function, using the year in cell C2, 11 as the month number, and 29 as the day. That is the first date after the latest possible Thanksgiving date (November 22-28).
DATE(C2,11,29)
We want the result to have a WEEKDAY value of 5 – a Thursday. So, the formula finds the weekday number for November 24th, which is 5 days prior to November 29th.
WEEKDAY(DATE(C2,11,24))
To calculate the Thanksgiving date, the completed formula is:
=DATE(C2,11,29)-WEEKDAY(DATE(C2,11,24))
Other Thanksgiving Calculations
There are other ways to calculate the date for Thanksgiving, and other floating holidays. For a few more examples, you can visit the Excel Holidays page on Chip Pearson's website.
Do you have another favourite method for calculating floating holidays?
Download the Excel Christmas Planner
If you're ready to start shopping and organizing your holiday activities, you can visit the Contextures website, and download the Excel Christmas Planner. It has sheets to help you with your Christmas budget planning, gift lists, dinner preparation, and much more.
Please let me know if you have suggestions for improving the planner!
Do you ever right-click on something in Excel, and the command that you wanted to use isn't on that pop-up menu? For example, if right-click on a cell, there is no command to turn off the gridlines.
Welcome back! The Contextures Blog was out of commission for a couple of weeks, and it's nice to be up and running again. A few of the shingles blew off during the reconstruction, so if you notice anything missing or broken, please let me know!
Recently, Jerry Latham showed us how to 
