General Drop Down
The General drop down in the middle of the Home ribbon has many standard formatting options that are easy to apply.
Monthly Archives: February 2015
The secret of genius is to carry the spirit of the child into old age, which means never losing your enthusiasm.
Aldous Huxley (1894-1963)
Identifying if an entry is one of a group
You have a number of options that require the same treatment. What is the easiest way to identify if an entry is one of a list?
Progress is impossible without change; and those who cannot change their minds cannot change anything.
George Bernard Shaw (1856-1950)
Excel 2013 Hide Filter Icon in a Table
This tip applies to Excel 2013 only.
If you have used the Format as Table option on a table you now have the ability to turn off the filter icons in the header row without losing the filter that has been applied.
The new Option is in the Table Style Option section of the DESIGN ribbon, see image below.
Using Excel For Business Analysis (Wiley)
Disclaimer: I received a free copy of this book to review.
If you’re after an introduction to using Excel for Financial Modelling, this is a good place to start. Danielle has even included shortcuts and explanations for the Mac version of Excel. For the beginner there are lots of good habits that you can learn. For the more experienced users there are explanations of how common bad habits can impact your models.
Just following her recommendations and using her tips could save you hours of work and also the frustration of “learning the hard way”.
Added June 2017 – You might want to check out Danielle’s latest book Financial Modeling in Excel For Dummies.
I have tried simply to write the best I can; sometimes I have good luck and write better than I can.
Ernest Hemmingway (1898-1961)
Excel – Change Format When Updated
Here’s the problem. You have a list of parts and prices, you want to manually update each price and you want the format to change for each price that has been updated. How can you do it?
Time is the most valuable thing a man can spend.
Theophrastus (372 BC – 287 BC)
VBA Functions
If want to see a list of the functions available in the VBA language just type
vba.
in the code window
You don’t need to include vba. when using the function in code.
If you see a function with a $ in its name it means it returns a string (text) eg UCase$
(Thanks to John Walkenbach for this tip.)
Click here to see how you can run a Macro off a Quick Access Toolbar icon.
Rocket Surgery Made Easy: The Do-It-Yourself Guide to Finding and Fixing Usability Problems (New Riders Publishing)
A great little book to help you test and improve your computer interfaces.
Easy to read and understand.
Uses humour and examples to get the points across.
How Not to Print an Image in Excel
If you want to place graphic objects on your sheet eg logos and checkboxes, but not have them print out, you need to change their properties.
Character consists of what you do on the third and fourth tries.
James A Michener (1907-1997)
Two Spaces
There are two types of spaces in Excel. The normal space and one that is called a non-breaking space, often found on websites.
When using some of Excel’s features Excel doesn’t recognise the other type of space. You can use the following formula to convert all non-breaking spaces in a cell to the normal space. Assume the text is cell A1.
=SUBSTITUTE(A1,CHAR(160),CHAR(32))
The CHAR function allows you to refer to ASCII characters by their number. 160 is the non-breaking space, 32 is the normal space.
This blog post discusses the CHAR function in more detail.
Switch: How to Change Things When Change Is Hard (Random House Business Book)
A good book. I preferred Made to Stick.
Switch contains great examples. Made to Stick was more prescriptive and easier to apply.
Switch requires a bit more lateral thinking but it does point out that change problems aren’t always people problems – they can be situational.
Your present circumstances don’t determine where you can go; they merely determine where you start.
Nido Qubein
How to use the WEEKDAY function
The WEEKDAY function allows you to convert all dates into a number from 1 to 7 representing their weekday, from Monday to Sunday.
I am not a product of my circumstances. I am a product of my decisions.
Stephen Covey
This website is a great resource for the DAX formula language used in PowerPivots in Excel 2010 and Excel 2013. The site contains solutions to common reporting requirements in PowerPivot Reports They have also published a book based on the content which is cheap as a Kindle edition, I have recently bought it.
The main focus is on presenting Health data but the content applies to all dashboards and charts. Some great advice on this blog.