Murphy Mac - Screencasts and Tutorials » Posts in 'Office' category

Record Smarter Excel Macros

Excel makes macro creation pretty easy with a capable recorder mechanism. You turn on the recorder, perform some activity in Excel, and the macro is written for you. This screencast was created on a Mac, but it wouldn’t be much different on a PC.

Excel MacrosThe main point of the screencast: How to use offsets to move the active cell around the worksheet. If you click on a bunch of cells while recording your macro the code will contain those exact cells. When you run the macro the same cells will be selected every time. Maybe that’s not what you want.

Example – what if the worksheet gets larger all the time, and you always want the macro performed on the last row? Or if you’re frequently manipulating cells to the right of the active cell? As long as your worksheet is laid out consistently macros can help.

In the screencast, Murphy starts by selecting an anchor cell at the top of a list and then using a keyboard shortcut to move to the bottom. The macro records the movement this way and always performs its actions at the bottom of the list. Murphy also shows how to move the cell pointer a certain number of cells left, right, up, or down from the current cell.

The bottom line: Using relative references in your macros allows the code to be effective as your spreadsheet grows. Combining relative references with an absolute starting point opens up a lot of possibilities.

PS: Voter turnout at the polls has been weak!!

Watch Now | Permalink

Excel Pivot Tables

Admittedly, Murphy is a little hung up on Excel this week. This is probably the end. But if you’ve never looked at a pivot table it’s worth taking a peek.

Pivot TablesWe’re going to use the same data we used yesterday, an accounting of Murphy’s gambling activity while in Vegas last week. The raw data doesn’t tell us much, but the pivot table gives us tons of presentation options.

There are some truly nifty features in a pivot table. Like double-clicking a sum to create a table displaying its source data. The interface is great too – friendly drag and drop. Even if your table set-up isn’t right you can probably drag a few things here and there to fix it.

You can base a pivot table on an Excel list or some other data source. In the screencast we’ll use an Excel list for simplicity.

Watch Now | Permalink

Excel Subtotals

The chips were flying last week while Murphy vacationed in Las Vegas. He kept a log detailing all his gaming sessions and entered all the data into Excel. The Subtotals feature made it really easy to summarize where the money went.

Excel SubtotalsA lot of Excel’s features for data management rely on what’s called “a list.” You can make certain tasks much easier by helping Excel recognize the boundaries of your list. How do you do that? Enter your data without any completely blank rows or columns. Excel sees the last row or column before a blank as the list’s edge. You can also apply some simple formatting to your header row (the column labels) so Excel won’t treat that row as part of your data.

Excel also has a List Manager feature to make your list more user friendly. But Murphy’s a little old-school when it comes to Excel, so he doesn’t often explicitly tell the application he’s making a list. You might like some of the features in a List Manager list, like a row at the bottom for adding more data.

Lots of people use Excel as a database. But with too much data it can become unwieldy. If you were to inadvertently highlight nine of your ten columns and then sort – that would be bad. The last column wouldn’t get sorted. Your data would be scrambled and you might not realize it for a while.

If you have data that truly requires a database it shouldn’t be stored in Excel. There are plenty of dedicated database applications to choose from. And Excel can still access your data in other programs to help with reporting.

Murphy looked at other Excel data features a few months ago. Take a look at this post to learn more about filtering your Excel data. Come back tomorrow for a look at Pivot Tables.

Watch Now | Permalink

Filtering in Excel

Smart FolderThis time of year you might be managing some big lists: Christmas bonuses for all your employees, shopping list, Christmas card list, who’s been naughty and who’s been nice…

If you’re like a lot of people you manage these lists in Excel. This Snippet shows a great feature for digging through your mountains of data. Autofilter can find the needle in your haystack.

There are other powerful tools for Excel lists, Murphy will get to those in 2007.

Watch Now | Permalink