General Drop Down
The General drop down in the middle of the Home ribbon has many standard formatting options that are easy to apply.
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? (more…)
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.
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? (more…)
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.
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. (more…)
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,…
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. (more…)