Excel Tips & Tricks

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.

Remedial Clicking

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.

Don’t Rely on Excel Too Much

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.

Excel can help your career

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.

PHP, MySQL and Entrepreneurism

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.

Analyzing Business Data with 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.

Using Excel on a Mac

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.

John Walkenbach knows Excel

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 while 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

• Allen Wyatt’s Excel Tips
• Jon’s Excel and Charting Pages

I eventually learned some more Excel tricks but then I spent too long studying and passing all three CFA® exams. Now I am once again looking for work and I think I should have long ago specialized in one specific technology instead of continuing to learn more new technology and techniques. If you have any Excel tips or tricks you can leave them below.

This entry was originaly posted on November 4th, 2006, it was last edited on February 27th, 2019 and is filed under: Technology and tagged: Allen Wyatt, John Walkenbach, MBA, Microsoft Excel, Statistics.