|
|
When you're analyzing data in an Excel pivot table, you might want to see the detail behind one of the numbers. To extract the data, you can double-click a data cell and a new worksheet is created, with the related records. This is a nice feature, but you'll end up with extra sheets in your workbook, and will need to clean things up occasionally. Filter the Source Data If the pivot table source data is in the same workbook, you can use the following macro, written by Héctor Miguel Orozco Díaz. It filters the source data, based on the pivot items connected to the double-clicked cell. For example, if you double-click the cell circled in screenshot below: the source data is filtered for Class_A, Month_3, Store_1, Code_A cost. This lets you focus on the detail records, without creating new worksheets. Download the Sample File Héctor's code is shown
Continue reading Filter Pivot Table Source Data in Excel
Over the weekend I did a backup of my RSS feeds, and created backup files for my WordPress blog. How often do I do this? Not often enough. So, I've added both items to my monthly task list, and that might help me remember to keep the backup files up to date. I also have a Maxtor external hard drive on both my computers, and they do an automatic overnight backup. How about you? Did you do a backup recently? _______________
In Excel, if you divide 2 by 8, the result is 0.25. if you format the cell as a fraction, the cell might show 1/4 as the result.
What if you want to show the result as a ratio? How can you get the cell to show 1:4 instead of 1/4? There may be other ways, but here's the formula that I used.
=B2/GCD(B2,C2) & ":" & C2/GCD(B2,C2)
This formula requires that the Analysis ToolPak be installed, in Excel 2003 and earlier versions. It divides each cell by the greatest common divisor (GCD), and puts a colon between the two numbers. Would you use a different formula? __________________
I've been reviewing Word documents recently, and sometimes I'm halfway down the document and have to check on something that was in an earlier part of the file. I could scroll up, to check the text, then scroll back down to the current spot, but that's not too efficient. It's easier, and more efficient, to split the Word window, so I can see two sections at the same time. To split the window, you can choose Window|Split, then click where you want the split. Another way to create a split it to point to the Split marker, at the top of the Vertical Scroll Bar, and drag it to the position you want. Remove the Split To remove the Split marker, double-click on it. ______________
What's on your desktop bookshelf? I only have a few inches of space on the bookshelf above my computer, and it holds the books that I refer to most often. From left to right, they are: Roget's Thesaurus French/English Dictionary Notebook Excel 2007 PivotTables Recipes Beginning Pivot Tables in Excel 2007 Excel Pivot Tables Recipe Book Manual of Style for Technical Publications Oxford Canadian Dictionary Binder with Contextures Procedures The thesaurus is about 100 years old, or at least looks that old. I've had it since university days, and like it much better than the newer style. Microsoft Word has a built in thesaurus feature, which I use occasionally, but it's no substitute for Roget's version. My books are there because I do lots of work with pivot tables and can't remember all the obscure issues and workarounds that I covered in the books. The binder doesn't have
Continue reading Desktop Reference Books
At the right of the Outlook 2007 you can display a To-Do Bar that shows a navigation calendar, a list of upcoming appointments and a task list. Very handy! When I open Outlook to check my email, I can see my calendar and tasks at a quick glance. Here's what the top of the To-Do Bar looks like. Unfortunately, it's not as useful as I first thought, since the To-Do Bar doesn't show all day events. So, if you've added a conference that will span a few days, or entered the birthdays for your friends and family members, those won't show up. Fortunately, my mom's birthday is in February, so I noticed this problem just in time! And if you didn't get a birthday card from me, it's Outlook's fault. Show Outlook Today I'll keep using the To-Do Bar, because I have a wide monitor, and the To-Do Bar
Continue reading No All Day Events in Outlook 2007 To-Do Bar
When you add a macro to an Excel workbook, users can see that macro in a list when they choose Tools|Macro|Macros. In some workbooks, you might want to hide one or more of your macros, to prevent users from running them. Note: This technique won't add any protection to your code, it will simply remove the macro from the list, so casual users won't accidentally or intentionally run it. Make the Macro Private In this example, I have two macros – one that the users can run, and one that I'd prefer to keep hidden. Here's the code: If I add the word Private at the start of the Sub MySecretMacro line, that macro won't be included in the macro list. Now only the MyVisibleMacro appears in the list. Run the Hidden Macro Now that you've hidden the macro, so casual
Continue reading Hide an Excel Macro
Moved to the Excel Twitters archive: Excel Twitters 20090110
Often an Excel worksheet is just a bit too wide to print on one page. You could change it to Landscape orientation, so more will fit across, but you may prefer to keep it in Portrait orientation, especially if it's a long list. In Excel's Page Setup dialog box, you can adjust the scaling so the sheet will fit across on one page. On the Page tab, click the 'Fit to' option for Scaling. Enter 1 as the number of page(s) wide. Clear the box for the number of pages tall, then click OK. This will limit the file to printing one page across, but won't limit the number of pages down. You don't have to guess the number of pages required, just leave it blank and Excel will figure it out for you. _____________
Continue reading Print An Excel Sheet On One Page
If you like to use keyboard shortcuts, you might have some of them memorized, in Word and Excel, or other programs. If your memory isn't too good, you can print a list of shortcuts and keep it near your computer as a reference. A few months ago, I mentioned some sites where you can download lists of Excel keyboard shortcuts. It's even easier to get a list of Word keyboard shortcuts. There's a built in macro that you can run, to create the list. Create a List of Word Keyboard Shortcuts In Word 2003, click the Tools menu, click Macro, then click Macros. In the Macros dialog box, click the dropdown arrow for 'Macros in' and select Word commands In the list of macros, click on ListCommands, then click Run. In the List Commands dialog box, click on Current menu and keyboard settings, then click OK A new document will
Continue reading Create a Word Keyboard Shortcuts List
|
|
Recent Comments