I was looking at a calendar and noticed it used alternately shaded cells, like a checkerboard, for all the dates and thought Excel could do that.
Monthly Archives: August 2016
Selecting Ranges
When selecting a range with the mouse or keyboard, keep an eye on the Name Box – top left corner of the screen, just above the grid.
It tells you how many rows (R) and columns (C) you have already selected – see image below.
As soon as you release the mouse, or stop using the keyboard, the values will disappear. You have to be quick to see it.
You never fail until you stop trying.
Albert Einstein
Handling Text and Real Numbers with VLOOKUP
Helping you work with imported data
Sometimes data that comes into Excel with code numbers formatted as text. This can stop VLOOKUP functions from working and return the dreaded #N/A error. With a couple of tweaks you can lookup both real numbers and text numbers in the one formula.
Without data you’re just another person with an opinion.
W. Edwards Deming
Date Entry Tricks
When entering dates from the current calendar year you can leave out the year. Excel assumes the current year.
Entering 27/7 and pressing Enter will enter 27/7/16. I wrote this post in 2016.
Whatever you enter is first considered as dd/mm. If that doesn’t make sense eg 9/15 then mm/yy if used.
If you enter a month and year combination it assumes the first of the month.
For example entering 9/15 will enter the 1/9/15.
You can use the forward slash / or the dash – between values in a date and Excel will recognise the date.
Excel treats 9/15 the same as 9-15.
To entry today’s date use Ctrl + ;
TRANSPOSE Function in Excel
Plus a trick to avoid zeroes displaying
The TRANSPOSE function is one of only a few functions that must be entered as an the array using keyboard entry Ctrl + Shift + Enter (CSE). It allows you to switch a range from going across the sheet, to go down the sheet and vice versa.
There are no shortcuts to any place worth going.
Hellen Keller
Insert a Pivot Table
To quickly insert a PivotTable in a new sheet, click in the data table and press, in sequence – not held down.
This uses the default data range.
PivotTable Listing Macro
Create a list of all PivotTables in a file
If you inherit a file or you haven’t used a file for a while, it can be useful to do an inventory of all the PivotTables. A macro can do all the work for you.
Always Refer to Cell A1
If you need to ALWAYS refer to cell A1, regardless of whether row or columns are inserted or deleted, then use the following formula.
=INDIRECT("A1")
This will always display the entry in cell A1 on the current sheet.
Another formula that always refers to cell A1 on the current sheet is
=INDEX(1:1048576,1,1)
Related Posts
I’ve learned that people will forget what you said, people will forget what you did, but people will never forget how you made them feel.
Maya Angelou
Getting a unique list in Excel
A PivotTable trick
I have blogged before about using the Advanced Filter technique to extract unique entries from a list. Well a PivotTable can do it too.