In Excel it is quite common to test a cell for either a zero or a blank. If either of these two entries are found then you do a particular calculation. There is an easy way to handle this.
Don’t wish it was easier, wish you were better.
Don’t wish for less problems, wish for more skills.
Don’t wish for less challenges, wish for more wisdom.
Jim Rohn (1930 – 2009)
Deep Work (2016)
A good book to encourage and motivate you to focus more.
Social media and the internet are designed to keep your eyeballs on the screen. This doesn’t get the worthwhile work done.
Moderation is tough in anything and with the dice loaded against you its hard to limit time on the net.
Some good suggestions and ideas to help you get more great work done.
I read this book with Essentialism by Greg McKeown. they compliment each other.
Bless their algorithmic hearts, Amazon are still trying to sell me my book.
It was released four years ago in April.
In the hopes of reaching the moon men fail to see the flowers that blossom at their feet.
Albert Schweitzer (1875 – 1965)
Peak Performance (2017)
Some great practical advice on areas to improve on.
Applies to many different areas of life.
Basic premise is simple, but some great suggestions on how you can improve in all areas of your life.
I recently read a blog post about using Excel for SEO (Search Engine Optimisation). It mentioned a function to extract a domain from a URL. The function was from Google docs, not Excel. So I wrote an Excel formula to extract the domain from their list of URLs.
Selecting a column
To quickly select a column of data in a formatted table you have a couple of options.
Select a cell in the column and press Ctrl + Space Bar.
This will select the column of data. If you want the heading too, press it again.
You can also select multiple columns before using the shortcut.
This technique can take practice if your headings are in row 1.
If the heading starts in row 2 or below it is easier. See image below.
If you point to just above the heading row you will see a downward facing, black arrow. Click this once to select just the data. Click it again to include the heading.
When the heading row is in row 1 you need to do the same but make sure the column letter doesn’t highlight.
The image below is the correct arrow – this will select the column in the table only.
In the image below the arrow shown (because the column letter is highlighted) will select the whole column, not just the data in the table.
If you don’t make mistakes, you’re not working on hard enough problems. And that’s a big mistake.
Frank Wilczek (Nobel Prize winner)
Barking Up The Wrong Tree (2017)
Eric has blogged for many years and this book follows on from his blog.
His writing style is relaxed and it is an easy read.
A book that turns some common advice on its head and confirms others.
Lots of studies are examined some of them conflicting, so you may need to experiment yourself with some of the practical suggestions.
Rounded values in Excel can pose a few issues. There is a formula you can use that can round a range of values and then SUM the results. This can be used as a check total for rounded values.
Coming back to where you started is not the same as never leaving.
Terry Pratchett (1948 – 2015)
Tribe of Mentors (2017)
Another great book from Tim Ferriss.
This has lots of short advice about life from successful people from all walks of life.
Its a smorgasbord – you can open it randomly and learn something new.
It follows on from his podcast and the Tools of Titans – another great book.
Many of his podcast questions are answered by people who haven’t been on the podcast.
You may have noticed that Excel gives every chart a unique number when it creates the chart. It is displayed in the Name Box in the left corner above the grid. You have the ability to change that name and make it more descriptive.
It is very important what not to do.
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.
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.