|
|
How do you save your file while working in Excel?
Do you click the Save button, and save over the previous version? Do you use Excel's AutoSave feature? Or the AutoSafe utility by Jan Karel Pieterse? Do you choose Save As, and save the file with a different name? Something else?
I like to have different versions of a file, so I can go back to a previous version if something goes horribly wrong. So, I created a macro to save my files, and added a button to the toolbar (or QAT in Excel 2007).
The macro saves the file in a specified folder, adding the year, month, day, hour and minute to the file name. For example, if the file I'm working on is named Budget2009.xls, the backup file would be named Budget2009_20081215_1008.xls if I saved it at 10:08 AM today.
The Macro Code
I store this code
Continue reading Excel Backups While You Work
Moved to the Excel Twitters archive:
Excel Twitters 20081213
No, you don't necessarily need treatment just because you like using Excel. ;-)
In response to a newsgroup posting, Roger Govier created a workbook to help patients plan a sequence of treatments. For example, people taking Warfarin, might be prescribed to take doses of 2 mg, 2mg, 3mg, 2mg, 5mg and then back to the start of the sequence.
In the sample workbook, they would enter that sequence on the Setup sheet.
Muscular Dystrophy sufferers need to inject at different sites on the body each day. They could list the sites on the setup sheet. After the sequence has been entered, click the button to have the sequence copied down to fill the Treatments Column.
View Treatments on a Calendar
After the treatment list is created, switch to the Calendar sheet, to set up the current month.
From the drop down lists, select a
Continue reading Excel Treatment Calendar
In a pivot table, you might have a few row labels or column labels that contain the text “(blank)”. This happens if data is missing in the source data. For example, in the source data, there might be a few sales orders that don’t have a Store number entered.
You want blank cells in the Row Labels area and Column Labels area to contain the text “N/A.” In the PivotTable Options dialog box, you entered N/A as the text to display in empty cells. However, the empty cells appear as (blank) in the Row and Column Labels areas.
Change the Labels
In the PivotTable Options dialog box, the setting for empty cells affects cells in the Values area, but not the Row or Column Labels areas. In the screen shot above, you can see that missing data in the Values area has been replace by N/A, but
Continue reading Change Blank Labels in a Pivot Table
If you use upper case for headings in Excel, spelling errors in them might go uncorrected, such as CAPITEL in the example below.
To make sure that the headings are included in a spelling check, you can change an option in Excel.
Change the Spelling option in Excel 2007 Click the Office button, then click the Excel Options button Click the Proofing category Remove the check mark from 'Ignore words in UPPERCASE' and then click OK
Change the Spelling option in Excel 2003 Click the Tools menu, then click Options Click the Spelling tab Remove the check mark from 'Ignore words in UPPERCASE' and then click OK
__________________
Roger Govier has created an Excel file with a macro that will set up a year's worth of workbooks for you, at the click of a button. It might not be the ideal workbook setup, but some people need to set these up, and this macro will certainly make the task easier.
This macro will create a series of 12 workbooks in the same folder as this workbook is stored. You'll be prompted to enter the year number at the beginning of the macro. Each new workbook will be named with month and year e.g. Jan 2009.xls through Dec 2009.xls
Within each workbook, there will be a sheet for each day of the month. There's an option to display the numbers as ordinals, so the sheet names would be Jan 1st, Jan 2nd and so on.
You can go to the Excel Sample Spreadsheets page and download the
Continue reading Create Excel Workbooks For the Entire Year
The UK Excel User Group is holding a free conference at Microsoft London in April 2009. It's a bit too far for me, but if you can make it, I highly recommend that you register. You'll learn new things, meet some terrific people, and spend a couple of days discussing Excel. What could possibly be better than that?
The agenda includes sessions on charts, pivot tables, functions, names and many other topics. Even if you're familiar with some of the topics, you'll benefit from attending. The presenters are a very smart and creative bunch, and they'll almost certainly show you a few tips and techniques that you haven't tried before.
The Q&A sessions will be an excellent opportunity to discuss any Excel problems that you've encountered, and get solutions or suggestions from the presenters and other attendees.
When and Where
Date: April 1-2, 2009 Location: Microsoft London (Cardinal Place) 100
Continue reading Free Excel Conference Microsoft London April 2009
Moved to the Excel Twitters archive:
Excel Twitters 20081206
I'm reviewing Word files and inserting comments for the author. I'd like to see my comments in balloons along the sidebar, but Word won't cooperate, and shows them in a Reviewing Pane, at the bottom of the window.
I have trouble editing my comments in the Reviewing Pane. Usually when you select text and start typing, the selected text is replaced with the new text. Not in the Reviewing Pane, unfortunately, where new text is inserted beside the selected text.
It's a real annoyance, and time waster. Editing works just fine in the balloons, which is one of the reasons that I prefer them.
How To Show Balloons in Word 2003
After a few minutes of searching through the menus and options, I figured out how to show the balloons.
First, choose either Print Layout view or Web Layout view. Balloon comments will only display in these
Continue reading Show Word Comments in Balloons
Sometimes there are blank cells in a pivot table's source data, and you might want to show a count of those blank cells in the pivot table. In this example there's an Employee field in the source data, and some of the records have no employee name entered. In the pivot table, you'd like to see how many records are missing an employee name.
No Count Appears
To find the count, your first instinct might be to:
add the employee field to the pivot table’s row area put another copy of the Employee field in the data area, as Count of Employee.
Although this approach seems logical, no count will appear for the blank employee names, as you can see below.
Why doesn't it work? There's nothing in those blank cells, so there's nothing for the pivot table to count. That's why no count appears beside the (blank) employee
Continue reading Count Blank Cells in Pivot Table Source Data
|
|
Recent Comments