Muschamp Rd

Excel Tips & Tricks

November 4th, 2006
Microsoft Excel logo

I’m pretty much self taught as far as Excel goes. I learned how to use Microsoft Word and PowerPoint better in my technical writing class at UVIC. I was surprised just how weak some people are at using Microsoft Word during my MBA program. People didn’t know how important styles are or how to automatically generate the table of contents for a report. None of my teammates during the MBA felt brave enough to use footnotes or even end notes and in most cases just getting people to properly attribute sources was a struggle. That said people manage to muddle through when it comes to Microsoft Word and PowerPoint but not knowing how to use Excel well left you at a definite disadvantage during an MBA program especially if you planned on specializing in Finance.

I attened the remedial clicking course as I dubbed it with Steve Keller. The guy teaching the clinic really knew Excel but even though his tutorial was aimed at people with only the most basic skills the class was uneven. He and other professors tried to pass on Excel tips when they could but it became really apparent during a Core statistics assignment how much effort you could save and how much better your assignment could look if you actually knew the ins and outs of Excel.

Besides relying on classmates to do work for you, there was a danger on relying too much on Excel as I learned during the Core Finance exam. I didn’t repeat that mistake and did every sample problem, many, many times with pen and paper for BAFI 500, which had a 100% final exam, something I was all too familiar with from my undergrad days. It was BAFI 500 along with the first post-Core accounting class that really altered the mindset of some of my Sauder MBA classmates. Many people were changing their specialization after a less than successful Period One. I never had to change my specialization but I do recall some Finance students organizing Excel tutorials and seeing complaints about the lack of Excel training in class surveys.

Since my MBA program ended I haven’t always used my time productively but one thing I have spent a lot of time doing is improving my resume. Having looked at more job postings than I care to count I’ve slowly altered my resume to have more keywords. Keywords aren’t just important to search engines, companies use automatic resume parsers and HR people quickly scan looking for keywords. As a result some more technical jargon re-entered my resume and in addition to old crap which I hopefully remember as I endeavored to learn new crap.

I read an epic book on PHP and MySQL but I’m not sure how much that will help me in my job search and I came away liking PHP and MySQL less and liking Java, WebObjects, and FrontBase more. I also read Jeffry Timmons 700 page tome on Entrepreneurism which also seemed overly long, but it was intended to be used as part of a course. Funnily enough, given the title of this posting, I’ve moved on to Excel.

The first book I read was entitled Analyzing Business Data with Excel. It wasn’t a bad book and specifically dealt with topics of interest to an MBA student. However it was not always possible to reproduce every screenshot given in the book. I often worked along through problems and this book gave the solution as a download, but would have benefited from providing more interim information. The fact I was a Macintosh user always further complicates things.

The three biggest things to keep in mind using Excel on a Mac are:

  1. To lock down cells, rows, or columns in a formula you need to press the ‘command key’ and ‘t’ together.
  2. To use an array formula you have to use the ‘command key’ and ‘return’ instead of just return.
  3. Finally many dialogue boxes are different on the Mac or just plain missing. One that is notably missing is PivotCharts. You can reproduce some of this functionality using the PivotTable tool and then creating the chart from the PivotTable.

The second Excel book I picked was John Walkenbach’s Favorite Excel Tips & Tricks. This book was actually less advanced than the previous one but I bought it instead of one specifically dealing with VBA as it was all about enhancing productivity when working with Excel. There is often more than one way to do something and muddling through isn’t usually the optimal solution. Without further ado here are a few Excel Tips & Tricks:

  • Setting up alternating row shading can be done using conditional formatting easily using the formula =MOD(ROW(), 2)=0
  • Learn how to take advantage of the AutoSum button
  • Returning the Last Nonblank Cell in a Column or Row use the following array formula =INDEX(D1:D500, MAX(ROW(D1:D500) * (D1:500<>” “))) This works on the first five hundred cells in column D
  • Creating a Self-Expanding Chart this is also easily done by selecting the data used by the chart and choosing Data->List->Create List which makes it a designated list using John Walkenbach’s terminology.

External Links

Posts on Muskblog © Andrew "Muskie" McKay comments not necessarily so...
CFA Institute does not endorse, promote or warrant the accuracy or quality of Muskblog. CFA® and Chartered Financial Analyst® are registered trademarks owned by CFA Institute.