March 2011

Table Design in Excel

March 31, 2011 Beginner

I’m want to start using Excel Tables when building my spreadsheets. But first it’s instructive for me to explain how I think about “unofficial” tables or data tables as I like to call them. How I Think About Tables A data table is just a range of data, or a region of data that Excel [...]

Get the full story …

Migrating to Tables in Excel

March 30, 2011 Beginner

My first epiphany with Excel was realizing how powerful it could be if you put all your data into data tables. Over the years Excel began to incorporate data tables. First in Excel 2003 as “Lists” then as official “Tables” in Excel 2007. (I’m not sure about the evolution in Excel for Mac versions.) The [...]

Get the full story …

My Short Hiatus

March 29, 2011 General Update

I broke with my usual posting frequency this week because I’ve been dealing with some plagiarism on my other website. I found out there’s such a thing as content scraping, which of course can be defeated. However, all of that took time and energy away from this blog, which was regrettable. Every Excel blog I’ve [...]

Get the full story …

Fat Fingers on the Space Bar – A Conditional Formatting Example

March 25, 2011 Features

I had the distinct pleasure of helping a colleague this week, because he fat-fingered the space bar whilst entering data in a spreadsheet then couldn’t figure out why there were #VALUE! errors on the Pivot Report. I used some conditional formatting to solve the problem and save the day. But to be fair, data entry [...]

Get the full story …

Speeding up VBA with the PrintCommunication Property in Excel 2010

March 23, 2011 Advanced

I have an Excel file that creates different reports from a data table using a macro, and before Excel 2010 was released the “time-hog” was running VBA code for the print settings. Tell me you’ve added something like the following code and had your execution time slow exponentially.  With ActiveSheet.PageSetup     .CenterHeader = "&""Verdana,Bold""&12&A" [...]

Get the full story …

Quickly Adjust 1904 Dates for the 1900 Date System in Excel

March 22, 2011 Intermediate

An associate of mine “helped” me out once by putting a worksheet together for data entry. After the worksheet had several days of user input, I found out the workbook was created on a Mac with an older version of Excel for Mac (2004) that defaulted to the 1904 date system. Since the worksheet in [...]

Get the full story …

Use Formula Auditing to Help Explain Formulas Excel

March 21, 2011 Features

When you need to present a complicated spreadsheet, the Formula Auditing feature in Excel visually helps to explain formula relationships. You can easily trace formula precedents or dependents. Think of precedents as “what comes before” or the inputs to a formula. If you need to show a formula precedent select a cell with a formula, [...]

Get the full story …

Documentation for VBA in Excel

March 16, 2011 Intermediate

A long time ago, in a far-away classroom I was seemingly forced to write copious amounts of documentation in a FORTRAN class just for a few lines of code. It didn’t help that I was just learning how to keyboard (type) and that I didn’t understand why pseudo code was also required by my nameless, [...]

Get the full story …

Putting Windows 7 on a Mac to Run Excel for Windows

March 14, 2011 General Update

I bought my first Mac almost 11 months ago and now comes the moment of truth. I need to go on the road and take my MacBook Pro with me, but also need to take several versions of Excel for Windows (2010, 2007, 2003) to validate and tweak an Excel reporting package. Note: Cross posted [...]

Get the full story …

Copy Data to Another Worksheet with Advanced Filter

March 9, 2011 Features

Dear Microsoft, please fix the Advanced Filter in Excel. Copying filtered data to another worksheet should be simple but it’s not. Fixing this would help out many of your beginning and intermediate users and some of us old dogs who seemingly haven’t learned the trick. The Problem with Advanced Filter in Excel I was reading [...]

Related Posts Plugin for WordPress, Blogger...
Get the full story …

← Previous Entries