When you are setting up a protected sheet that allows users to make inputs, a few option changes can make the user’s experience a lot easier.
It is very important what not to do.
Knowledge is the beginning of practice; doing is the completion of knowing.
We’ve all been there, our charts are looking just right and then some one inserts a column or changes the column width and throws out all our perfectly proportioned charts.
VBA If statement tip
When creating long VBA code it is common that the start of an If statement and the matching End If statement may not be visible on the same screen.
When scrolling around trying to understand your code it can be useful to include the If statement itself as a comment following on the same line as the End If command – see examples below.
If x=0 Then 'lots of code If y =1 Then 'lots of code End If 'If y =1 then End If 'If x=0 Then
The apostrophe is used to specify the start of a comment – you can have a comment following a line of code.
This structure can assist when trying to identify which End If statement relates to which If statement.
A schedule defends from chaos and whim. It is a net for catching days.
Power Query can easily combine data from multiple Excel files. A problem can arise if one or more of the files is open. Power Query will generate an error and the import will fail. The solution involves an old school Excel feature.
Entering Date and Time in Excel
There are shortcuts to enter
- the current Date Ctr + ;
- the current Time Ctrl + Shift + :
There isn’t one to enter both.
You can use them in sequence to achieve a date and a time entry.
In sequence press
Ctrl + ;
Ctrl + Shift + :
The space separates the date and time and Excel will recognise the entry as a date and time.
Our fears are always more numerous than our dangers.
Seneca the Younger
Let’s say you have codes that have differing numbers of characters and you need to analyse them based on how many characters a code has. There is one function that can SUM and COUNT based on the number of characters in a code.
If you change a formula in someone else’s file you should tell them. In fact there are a few things you should do to make it easier. This can apply to changes to your own files as well.
It is not what you look at that matters it is what your see.
Henry David Thoreau
Getting dates into order is usually a job for Power Query, but not everyone has it or uses it so I still get requests for formulas to fix text dates.
Get & Transformation
Power Query morphed into Get & Transform and now in the latest Excel 2016 subscription release Get & Transform has expanded on the Data Ribbon.
Action may not always bring happiness, but there is no happiness without action.
Benjamin Disraeli (1804–1881)
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.
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)