Let’s assume you have three state codes and four department codes and you want to create a table of all the possible 12 combinations (3 x 4). How do you do it so that it is flexible? i.e. if you add a new state or department it must be easy to update the combination table.
Action may not always bring happiness, but there is no happiness without action.
Benjamin Disraeli (1804–1881)
The future belongs to those who believe in the beauty of their dreams.
Excel will automatically decrease the print zoom % to fit to one page, but it won’t increase the zoom % to fit to one page. E.g. if you want to print on A3 instead of A4. I had a request to do this, so I wrote a macro to do it.
Filtering blanks made easy
In Excel the “Blanks” option is usually at the bottom of the list. This slows down selecting it.
If you have a lot of entries you need to scroll all the way down to bottom of the list to choose it – see image below.
But the word “Blanks” is searchable, so if you type b in the Search box – your work is done – no scrolling required – see image below.
If your column contains text you might need to type in bla.
Give me six hours to chop down a tree and I’ll spend the first four sharpening the axe.
Abraham Lincoln (1809 to 1865)
There are certain situations when you can’t “unhide” columns on the left and/or rows at the top and it is not a sheet protection issue.
Time goes, you say? Ah no! Alas, Time stays, we go.
Henry Austin Dobson (1840 – 1921)
There are a number of mouse and keyboard shortcuts for copying. But there is one type of copy that can be frustrating. Copying dates can be challenging because, in general, Excel wants to increment them, not copy them. There is a simple technique to instruct Excel to copy a date.
A library is thought in cold storage.
Herbert Samuel (1870 – 1963)
Columns in a Text Box
You learn something new every day.
Today I discovered that you can have columns within a text box.
If you right click the text box and choose Format Shape the Task Pane below should open on the right.
Click the third icon (Size and Properties) at the top and then open up the Text Box options.
The Columns button allows you to specify how many columns plus the gap between them.
Excel 2016 has introduced a new type of IF function to simplify handling multiple conditions. It is called IFS.
Woohoo – my book made it to 3 on the top ten e-books in the CPA Library.
A man travels the world over in search of what he needs and returns home to find it.
George Moore (1852 – 1933)
Some systems add DR and CR to the end of numbers when they export into Excel. This renders the values useless for normal calculations. You can use data cleansing techniques to remove the characters using formulas or Power Query. There is one function however that can perform calculations on these types of entries.
I cannot remember the books I have read any more than the meals I have eaten; even so, they have made me.
Ralph Waldo Emerson
Charts have a behaviour that many people don’t realise. That behaviour can also be turned off. If you hide a row or column in the data range used by a chart, the values will also be hidden on the chart.
A little knowledge that acts is worth infinitely more than knowledge that is idle.
Kahlil Gibran (1882 to 1931)
When clearing page breaks in Excel VBA you need to be careful. There is one command that will clear page breaks but it will also affect other print settings.