Category Archives: Excel shortcuts

99-Spreadsheet modeling, Excel power users, Excel resources, Excel shortcuts

Every Spreadsheet Has An Error

2 Comments

If your job is anything like mine, you’ve had to work on a massive data dump, sorting and manipulating to find “a story.” The size of the data files can sometimes be intimidating and there sometimes is that concern in the back of your mind that some formula or reference within your Excel workbook went rogue.

As we strive to be more proficient in our Excel skills and more efficient in our tasks, coming across a headline such as “Every Spreadsheet Has an Error” can certainly sound alarming! However, as I read the article in greater depth, I found the points to serve as good guidelines to help check my work.
http://www.forbes.com/sites/billconerly/2013/04/25/every-spreadsheet-has-an-error-7-lessons-motivated-by-reinhart-and-rogoff/
Some of the tips suggested in the link are:

1) Use assumption variables – Similar to what we learned in Decision Analysis class, create a section within your workbook with all your assumption variables. Create links within the workbook to these assumptions. This will ensure that when you have to change your assumptions in the future you will not have to search all your data for every occurrence that may be affected.

2) Link, don’t copy – Create links to raw data so that you can go back and check your manipulated data against the original document.

3) Create double-check formulas – “If, then” and “true, false” statements are great for checking your work!

4) Format to Tell Differences – Conditional formatting helps to highlight differences in the data. They also help to identify trends and patterns in your data. I’m a big fan of using colors when doing conditional formatting.

5) Graph Your Data – When possible, graph your data. A line chart for instance will give you a quick visual to not only spot any irregularities in your data, but also to help find “the story.”

6) Document – Create notes of the steps or sources that you used to create your end product. This habit can save a ton of time when you have to do an update. Notes are also helpful for others who may have to replicate your report. We have a saying at my workplace, “a detailed source line is not for the client, it’s for us!”

7) Be Suspicious – Check your work. See if you can find an error.

Hopefully, by following some of these tips, you found all of your errors!

99-Spreadsheet modeling, Excel resources, Excel shortcuts

Pharmacist with Advanced Excel Skills, an Oxymoron?

1 Comment

I have now come to the conclusion that a pharmacist with advanced Excel skills is an oxymoron.

To prove this I did an advanced linked in search a couple of months ago with the key words “pharmacist” and “excel”. I was surprised when I got a lot more hits than I expected. I started to panic. However, when I started going through the profiles, I realized that people were describing how they excelled at say patient care or customer service etc. rather than at MS Excel.  While this brought a sigh of relief that I wasn’t alone in the boat of pharmacists bereft of excel knowledge, it made me also realize the huge opportunity that existed for me.

My journey into the world of excel has been through a variety or resources, but I have found wallstreetprep.com particularly helpful.

While I’m still a work-in-progress, I am encouraged by what I am able to do so far and wish to send that encouragement on to my other colleagues, who by their nature of work never have to touch a spreadsheet. It is doable. All it takes is a couple of minutes a day consistently.

99-Spreadsheet modeling, Excel resources, Excel shortcuts

Some Excel Resources to Get you Started

Leave a comment

Those of you choosing to focus on your spreadsheet modeling skills this summer (and everyone else who hopes to devote some time to that at some point) should download the materials I’ve just added to our Blackboard site. (You’ll see a new section, Excel Resources, in the left side-bar.)

The main deck, the “Three Hour Tour,” is an enormous collection. For some of you, all you really need may be found in a small number of slides, for example:

  • The “Top 10” and “Next 10” lists of Excel skills that are essential for MBA work are found on slide #162.
  • Lists of print and web resources for learning and using Excel are found in Appendix 3, starting on slide #193.

Also, for those of you looking for keyboard shortcuts, those new resources on Blackboard include a compilation of several sources’ recommendations and cheat-sheets for Excel and some of the other MS Office applications.

I’ll contribute some more Excel content here in the 05-Spreadsheet Modeling channel of our blog, but of course part of the point is for you to do the same – please share your own discoveries and experiences, and get some discussions going here and elsewhere on the blog.