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 sometimes need to fix data that has numbers, with trailing minus signs? I don't run into this problem too often, but I usually fix them with the Text to Columns feature.
Select the column that contains the trailing minus signs
On the Excel Ribbon's Data tab, click Text to Columns.
Click the Next button in Steps 1 and 2.
In Step 3, click the Advanced button.
Add a check mark to the option, "Trailing Minus for Negative Numbers"
Click OK, and then click Finish.
The minus signs are magically moved to the front of the numbers, and they'll calculate correctly if you include them in formulas.
Fix Trailing Minus Signs With a Formula
If you need to fix trailing minus signs frequently, you might prefer a formula solution. With a formula, the numbers are fixed without your manual intervention, as soon as the data is pasted into the worksheet.
Thanks to Bob Ryan, from Simply Learning Excel, who sent this formula to fix imported numbers with trailing minus signs.
In this example, the first number with a trailing minus sign is in cell A1
In the formula, the RIGHT function returns the last character in cell A1.
If that character is a minus sign, the VALUE function returns the number value to the left of the trailing minus sign.
The minus sign before the VALUE function changes the value to a negative amount.
How Do You Fix Trailing Minus Signs?
Do you use another solution for trailing minus signs? There is a Trailing Minus Signs macro on the Contextures website, if you want to automate the fix.
In a comment on the Sort a Row in Excel 2010 blog post, Debbie asked about sorting 2000 rows, left to right. She didn't say they were lottery numbers, but her example, shown below, sure looks like that to me.
One way to sort the rows is to use a formula, in columns to the right.
In the screen shot below, cells H1:M1 are selected, and this SMALL formula is entered:
=SMALL(A1:F1,{1,2,3,4,5,6})
Then, to array-enter the formula, press Ctrl+Shift+Enter
Then, copy the formula down to the last row of numbers, to see all the rows in ascending order.
As a final step, you could copy the columns of formulas, and paste them as values.
Sort Multiple Rows with a Macro
If you don't want to mess with formulas, you could use a macro to sort each row, left to right. This macro, from Dave Peterson, will sort all the rows on the active sheet, starting in row 1, and assumes there are 6 columns of numbers.
Make a copy of your original worksheet, before sorting with the macro.
Sub SortLotteryRows()
'posted by Dave Peterson
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
With ActiveSheet
FirstRow = 1 'change to 2 if there are headings
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = FirstRow To LastRow
With .Cells(iRow, "A").Resize(1, 6)
.Sort Key1:=.Columns(1), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlLeftToRight
End With
Next iRow
End With
End Sub
Other Row Sorting Ideas
Do you have any other ideas for sorting lots of rows? Please share your ideas in the comments.
Do you feel deprived? There's no built-in bullet magic for Excel cells, like there is in a Word document.
Fortunately, you can add a bullet to a cell with the keyboard shortcut:
Alt + 0149
However, this shortcut only works if you type the numbers on the Number keypad – not the numbers at the top of the keyboard. If you're using a laptop, use the special keys to activate the "number keypad" section of the keyboard.
Bullets and Numbering Command
If you're adding notes to an Excel worksheet, you can put the text in a textbox, instead of using the cells.
In a textbox, there are a few more formatting options, including a Bullets command. Last week, Bob Ryan sent me a Student Budget workbook, and he had the instructions in formatted textboxes, including bullet points.
To add bullets to your text boxes in Excel 2010, right-click on the text, and click Bullets, in the popup menu. In Excel 2007, the Bullet command is on the popup formatting toolbar, just above the right-click menu.
Then select the bullet style that you want.
Or, click the Bullets and Numbering command, to see more options.
Watch the Excel Bullets Video
To see the steps for adding bullets in a cell, or in a textbox, you can watch this short Excel video tutorial.
Last week I ran into an interesting problem, when trying to fix a macro button in a client's Excel file. I've created a simplified version of the workbook and button, to show you what happened.
The workbook had been converted from Excel 2003 to Excel 2010, and when the macro button was clicked, it showed an error message.
Cannot run the macro...The macro may not be available in this workbook or all macros may be disabled.
Other buttons in the workbook were working fine, so the macros were enabled – that wasn't the problem.
Assign a Macro to the Button
I tried to reconnect the button to the macro, but when I tried, another error message appeared.
Reference must be to a macro sheet.
Well, I haven't used a macro sheet for about 10 years, so that was a bit confusing! Didn't we get rid of macro sheets, several versions ago?
Cell Name Conflict
Finally, I realized what the problem was. The person who created the macro used a very short name – MBT1 – which was fine in Excel 2003, where the last column was IV.
When the file was converted to Excel 2010, the macro name became a problem, because there is a cell with the address MBT1. During the conversion, the button's macro reference was automatically changed to _MBT1 to prevent a conflict with the cell reference.
However, the macro name wasn't changed automatically – it was still named MTB1. So, the button couldn't find a macro named _MTB1, and the error message appeared.
Fix the Macro Name Conflict
To fix the problem, I changed the macro name to MTB1_Macro, and connected the button to the renamed macro. Now, the button works correctly, and runs the macro without complaining.
Prevent the Problem
When you're recording a macro, if you use a name that's the same as a cell name in Excel 2010, you'll see an error message, warning you about the name conflict.
The name conflicts with an Excel built-in name or the name of another object in the workbook.
Use longer names for your macros, or include an underscore, to avoid problems – now, and in the future. Who knows how many columns the next version of Excel will have?
Other Macro Naming Problems
You can also have problems running your macros if the procedures have the same name as a module in the workbook. To avoid that, start your module names with "mod", such as modUpdate, and don't use those names for any macros.
Have you run into any other problems with naming your macros?
There are Excel formulas and User Defined Functions (UDF) that can change numbers into words. Those are handy if you're typing a number into a workbook, and want the written amount to be shown, as it might appear in a cheque.
Have you ever tried to do the opposite – change words into numbers? It's a tricky process, and Excel MVP, Jerry Latham, has created an Excel UDF – WordsToDigits – to help you out.
Words to Numbers
With the WordsToDigits UDF, you can create a worksheet formula to translate words into their numeric value. For example:
Five hundred fifty-two becomes 552
One dollar and 27 cents becomes 1.27
In the first example, the words are in cell B2, and the formula in cell C2 is:
=WordsToDigits(B2)
You can format the formula results in any number format you want, such as general numbers, numbers with thousands separators, or currency.
Refer to Worksheet Cells
Although the WordsToDigits UDF is quite flexible, it only works with cell references, not text strings. For example, this formula in cell C2:
=WordsToDigits("twelve")
will result in a #VALUE! Error.
However, if you type "twelve" in cell B3, and refer to that cell in the formula, the result is correct. Here is the formula that's entered in cell C3:
=WordsToDigits(B3)
WordsToDigits Tips
Jerry passed along these tips, for working with the WordsToDigits UDF:
It can handle numbers up to 999,999,999,999,999.
The accuracy of any decimal portion decreases as the number of digits increases. But at normally used value ranges such as up through millions, this should not be a significant issue.
Any fractional parts of a number must be entered as digits, as:
six and 7/100
twelve hundred dollars and 27 cents
Misspelling of numeric words will result in them being interpreted as zero, so typos can give inaccurate results.
Note: The WordsToDigits UDF is based on the short scale system of naming large numbers.
Experiment With the WordsToDigits UDF
You can download Jerry's WordsToDigits workbook, to see the UDF code, and his examples of how to use the function. To test the UDF, you can create your own entries on the workbook's Sample Entries sheet.
If you have questions or comments, please share them with Jerry, in the comments below.
Monday, October 17th, was Spreadsheet Day, and I hope you've recovered from all the festivities.
Thanks to everyone who tweeted about Spreadsheet Day, and a special thanks to those who made a Spreadsheet Day post on their blog. In case you missed any of the posts, here's a list. If I omitted yours, please let me know.
Plan and Track Student Spending
Pivot tables summarize the key information in this student budget workbook, from Bob Ryan, of Simply Learning Excel.
Student Time Tracker
Keep tabs on class times and assignment workload, with my Student Time Tracker. Then, when you become a Microsoft Office consultant, you can use the same technique to track meetings and project work.
Happy Spreadsheet Day
A lesson in the perils of gambling, from Mike Alexander, of Bacon Bits blog. Using a spreadsheet to track your gambling losses doesn't lessen the pain.
Automating Class Creation
Students attend classes, so Dick Kusleika, from Daily Dose of Excel, shows his technique for automating class creation in Excel VBA. The video demo is silent, so you can play the music of your choice, or add your own voice over.
Experiment With Excel
You don't need extravagant plans to celebrate Spreadsheet Day. To quote The Science Goddess, "So, my advice for today is simply to go forth and double-click. Open Excel and play around."
Celebrate Responsibly
Finally, wise words from Excel guru, and party pooper, John Walkenbach – celebrate Spreadsheet Day responsibly.
Happy Spreadsheet Day! I hope you're making time to cell-ebrate this special day. October 17th was selected as Spreadsheet Day, because that is the date that VisiCalc was first shipped.
Student Spreadsheets
The theme for this year's Spreadsheet Day is Student Spreadsheets. If you have uploaded a free, useful template or add-in for students, or posted a spreadsheet tip, please send me the link so that I can share it.
Or, post your links/tips on Twitter, using the hashtag -- #spreadsheetday – so we can find them.
Student Time Tracker
My contribution for Spreadsheet Day 2011 is a Student Time Tracker. You can keep track of your lecture hours, and course work hours, to see what the weekly totals are.
To start, you'll enter the Semester start and end dates, in the blue cells.
Next, enter your courses, and the scheduled lecture and lab hours per week.
Add Your Assignments
As the semester progresses, enter any assignments that you get, and other tasks, like preparing for tests and exams. As you finish your assignments, enter the completed date and actual task time. This will help you improve your time estimating skills.
For large assignments, you can use the Course Work Time Estimator sheet, to enter all the steps, and the time each step should take. Then, add a buffer percentage, to include extra time in the estimate. This will cover all those little things that can go wrong along the way.
Check the Weekly Hours
On the Weekly Hours Time Estimator sheet, you can see the total hours for each week in the semester. The Work Hours are calculated by using the SUMIF function to get the hours for each week.
At the top of the sheet, enter your target hours for each week – the maximum number of hours that you want to spend on classes and assignments.
In the screen shot below, the target is 25 hours (that's pretty low!), and there is conditional formatting to highlight weeks that exceed that target.
If you see a heavy week coming up, you might be able to complete some assignments early, to ease the workload.
Download the Student Time Tracker
To see how the time tracker works, you can download Excel Student Time Tracker template. The file is in Excel 2007/2010 format, and zipped. There are no macros in the file.
If you have any suggestions for improving the workbook, please let me know.
Are you ready for Spreadsheet Day on Monday (October 17th)? I'm just back from a week of vacation, and will be working on my "Help a Student" template this weekend.
Yes, I crossed the border this week, and spent time shopping and relaxing in Rochester NY. I toured the home of George Eastman, founder of the Kodak company, and will post some photos next week. He wasn't a spreadsheet guy, but some of his ideas can be applied to spreadsheets.
Spreadsheet Day
I hope you'll cell-ebrate Spreadsheet Day on Monday, and post a link to your free template, add-in or spreadsheet tip, that will help a student succeed.
The Science Goddess, on the Excel for Educators blog, has asked teachers to suggest spreadsheets that would be useful to their students. As she says to her readers, "if you don't talk to your kids about spreadsheets...who will?"
If you need inspiration for the Spreadsheet Day challenge, wander over to the Science Goddess' blog, and see if there are any template suggestions.
Spreadsheets Everywhere
Even on my vacation, I saw spreadsheet everywhere, like this table in the hotel. Maybe it's just me, but those tiles look like the cells in a spreadsheet. I'm sure that the Margaritas didn't affect my interpretation of the table pattern. ;-)
Please Contribute
Please take a few minutes on Monday to contribute to the Spreadsheet Day cell-ebrations.
Post a spreadsheet tip on Twitter, with the #spreadsheetday hashtag.
Write an article with a spreadsheet tip for students
Create a student-themed spreadsheet template or add-in and post it on the internet
Remember to let me know about your post, so I can link to it on the Spreadsheet Day blog
Recently, Jerry Latham showed us how to 
There are Excel formulas and User Defined Functions (UDF) that can 
Monday, October 17th, was Spreadsheet Day, and I hope you've recovered from all the festivities. 
Happy Spreadsheet Day! I hope you're making time to cell-ebrate this special day. October 17th was selected as 
Are you ready for 
