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)

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.

Filter_1

Related Posts

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)

VBA Functions

If want to see a list of the functions available in the VBA language just type

vba.

in the code window

VBA_Tip_1

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$

VBA_Tip_2

(Thanks to John Walkenbach for this tip.)

Click here to see how you can run a Macro off a Quick Access Toolbar icon.

Related Posts

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.

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.

Great DAX formula site

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.