Moving and Navigating in Excel

Move...That...COLUMN!

Remember, to pick up a column and move it, here's the batting order:
  1. Select the entire column.
  2. Put your cursor on the left edge of the column. If you use Windows, your cursor should look like a four-headed arrow; if you use a Mac, it should look like Mickey Mouse's gloved hand.
  3. Hold down your SHIFT key
  4. Click and drag the column until you see a gray line between the two columns where you want to put it
  5. Let go of the mouse button first, then the SHIFT key

Move from One Worksheet to Another

If it's just the next worksheet up or down:
Windows users - CTRL+PGDN and CTRL+PGUP
Mac users:OPTION+RIGHT ARROW and OPTION+LEFT ARROW

If it's across multiple worksheets:
Go to the lower-left corner of the screen, where you see the "fast-forward" buttons. Right-click, and you'll see a list of all of your worksheets.

Select a Group of Cells

To select down, use CTRL+SHIFT+DOWN ARROW (Mac users, use COMMAND instead of CONTROL)
To select across, use CTRL+SHIFT+RIGHT ARROW (Mac users, use COMMAND instead of CONTROL)

Randomization

Random Numbers, Letters, and Such

Generate a random number between x and y (example: 10 and 20)
=INT(RAND()*(20-10)+10)

Generate a random, capital letter:

=CHAR(RAND()*(90-65)+65)

Generate a random number or letter:

=MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",36*RAND()+1,1)

Randomizing Addendum

In Excel 2007 or above on the PC and 2011 for the Mac, there's now a function called RANDBETWEEN, which, as its name implies, lets you select a random number between x and y:
=RANDBETWEEN(1,10) would be a random number between 1 and 10, for example.

(Or perhaps you prefer Groucho Marx's conversation with Margaret Dumont in Duck Soup:

"Do you rhumba?"
"Why yes, I do."
"Good. Give me a rhumba between one and ten."

I can hear your groans from here.)

Text - Search and Destroy Missions

Repeat a Character

Suppose you have a field on a report that has to contain a number of characters, like, 25. Most of the values are only 7-10 characters each, and you want to “pad” the cell to make it exactly 25 characters. The formula is written assuming that the @ character is the character to use for padding, and A2 is the cell with the value:
=REPT("@",25-(LEN(A2)))&A2

What Cell is It?

If you survived Using Excel Functions, you know how to use MATCH, INDEX/MATCH, and VLOOKUP to find a value in a list. What if you want to know the address in which that value occurs?

So first let’s assume you’ve matched a value successfully:
=(MATCH($A$10,$B$1:$B$8,FALSE))

Let’s find out what the address of that cell is:
=(ADDRESS(MATCH($A$10,$B$1:$B$8),1))
with the MATCH function now helping us get the row, and the 1 referring to the first column, returning $A$8

A Better Date Calculation

In the PivotTables class, we learned how to get the year difference between two dates. But there’s a more precise (if poorly documented) method to get the true date. Assume in this exercise that the student’s DOB is in A2:
=DATEDIF(A2,NOW(),”y”)

The function DATEDIF takes three arguments: the earlier of the two dates, the later of the two dates, and the interval. The interval can be: y (year), m (month), d (day) in its simplest form. To get more precise, you can write this formula:
=DATEDIF(D2,NOW(),"y")&" years, "&DATEDIF(D2,NOW(),"ym")&" months, "&DATEDIF(D2,NOW(),"md")&" days"

In this case, “y” is the years, while “ym” is the number of months leftover that don’t add up to a year, and “md” is the number of days leftover that don’t’ add up to a month.

Get the Last Piece of Text After a Character

My state (CA) requires us to report suspensions and expulsions using Incident Management.

To make a long story short, I had to get a report for my boss based on the actions taken.

The query is one I modified from the PDS Custom Report, and I couldn't have done it without them. (thanks guys!)

When I finished my query, I often had cases where the Action read like this:
(100) Suspension for School Days& (100) Suspension for School Days& (200) Expulsion for School Days
What I really needed was the (200) Expulsion for School Days at the end.

I had to figure out how to grab all the characters to the right of the last ampersand. On top of that, I had to make sure that if I had any cells that just said (100) Suspension for School Days, leave that alone.

Thanks to the wonderful world of Google, I found an answer that I could modify for my own uses. In brief:
  • Check to see if the cell has an ampersand. If not, just leave it alone, and give me that value.
  • If it has an ampersand, then substitute all the ampersands with slashes
  • Then, grab everything to the right of the slash

Tricky tricky. :)

=IF(ISERROR(FIND("&",K2)),TRIM(K2),TRIM(RIGHT(K2,LEN(K2)-FIND("\",SUBSTITUTE(K2,"&","\",LEN(K2)-LEN(SUBSTITUTE(K2,"&","")))))))

Keyboard Shortcuts You Might Not Have Known

Most of these are accidental discoveries; most of you have heard my praise for those:


CTRL

SHIFT

1

Number with commas



CTRL

SHIFT

2

Time



CTRL

SHIFT

3

Date



CTRL

SHIFT

4

Currency



CTRL

SHIFT

5

Percent



CTRL

SHIFT

6

Exponent



CTRL

SHIFT

7

Border on all four sides



CTRL

SHIFT

~

Number with no commas



CTRL



`

Show the formula, not the number