Create sequential table rows and columns
Tools: custom lists, autofill
Level: beginner
Problem
Most of the times, when you start working in Excel, you will need to fill out a square matrix with one or more row of column headers and one or more column of row headers. For example,
| January | February | March | |
A straightforward and very time consuming way to do so is to type the number or text series manually (and possibly make some typing mistakes). There is, however, way to do the same in a matter of seconds. We can use a feature called AUTOFILL and CUSTOM LISTS.
Solution
- Active cell - first cell where your data or text series should start.
- Type the first sequential number or text string in the first cell.
- Move one cell down or right.
- Type the second sequential number or text sting in the second cell.
- Select the two cells you just filled up.
- Grab with the mouse a FILL HANDLE a small rectangle in the bottom right corner of the selection border.
- Drag down or right. The cursor will show a label displaying what will be the final value in the list if you stop at current position. You can also drag up or left, in this case the series will be filled backwards!
- Stop and release the mouse button. The selected space will be filled with a number or text sequence that continues the initial cells.
For lists that are tens or hundreds or items long, we can use a different method
Similar tasks
Excel can extend a limited type of lists, but they cover about 80% of all possible needs.
| List | What you type | What you get |
| Numbers | 1, 2 | 3, 4, 5, 6 |
| Time | 9:00 | 10:00, 11:00, 12:00 |
| Weekdays (abbr.) | Mon | Tue, Wed, Thu |
| Weekdays | Monday | Tuesday, Wednesday, Thursday |
| Months | Jan | Feb, Mar, Apr |
| Each nth month | Jan, Apr | Jul, Oct, Jan |
| Each nth month and year | Jan-99, Apr-99 | Jul-99, Oct-99, Jan-00 |
| Each nth month | 1-Jan, 1-Mar | 1-May, 1-Jul, 1-Sep |
| Each nth days of each nth month | 15-Jan, 15-Apr | 15-Jul, 15-Oct |
| Quarters | Qtr3 (or Q3 or Quarter3) | Qtr4, Qtr1, Qtr2,... |
| Years | 1999, 2000 | 2001, 2002, 2003 |
| Text strings with numbers | Product 1 | Product 2, Product 3,... |
| Text string with numbers and prefixes | 1st Period | 2nd Period, 3rd Period,... |
| Repeating text sequences | text1, textA | text2, textA, text3, textA,... |
If you need to create on a regular basis a list that does not fit any of these lists, you can create your own CUSTOM LIST. Use menu or ribbon command Tools-Options-Custom Lists.
Fill blank range
Tools: keyboard navigation and selection shortcuts
Level: beginner
Problem
You have a table that you need to fill out with a common formula.
| A | B | C | D | |
| Order | Price | Quantity | Total | |
| =B2*C2 | ||||
|
|
|
What would you do next? Granted, you can use fill handle and copy the formula down but once the spreadsheet starts scrolling down, you can pick up too much speed and wind up copying too little, or possibly, way too much.
There is a faster and nearly effortless solution that does not use mouse at all.
Solution
Make an 'anchor' at the bottom of your list where it ends and copy down to it. We start at cell D2 and will use combinations of Ctrl and arrow keys (Left, Right, Up, Down) to jump to borders between non-empty and empty cells.
- Active cell D2.
- Ctrl-C. Copy the formula (or whatever this cell contains).
- Ctrl-Left. Cursor moves to the left border of block of non-empty cells where you currently in. This happens to be the first column that is already filled down as much as you need. Cell A2.
- Ctrl-Down. Cursor moves to the bottom border of block of non-empty cells. Cell A2001.
- Ctrl-Right. As you may already guessed, the cursor jumps to the right border of the text block. If you only have a couple of lines like in this example, a few bangs on the Right key might be faster, but if your block has several or several dozen columns, this makes more sense. Use your judgment based on context of your spreadsheet. Cell C2001.
- Release Ctrl. One more Right key press to return to column D. Cell D2001.
- Enter. The content you copied is pasted in the last cell. You have now filled the first and the last cells of the range you need to fill.
- Ctrl-C. Copy the formula in D2001.
- Ctrl-Shift-Up. This command with make the cursor jump to next non-empty cell. But since you now have the Shift key pressed too, the space between Cell 201 and Cell D2 will all be selected.
- Enter. The range you selected is filled with formula. Mission accomplished.
Initially, it will take you some time, but as your fingers learn the key positions, it will take you 1-2 seconds, while your mind will be totally free from thinking what you do. All you will think of is "fill this space", and before you finish this thought, the task will be done.
Similar problems
Task 1. Move a rectangular block of cells.
- Active cell - top left corner of the block
- Ctrl-Shift.
- Right.
- Down.
- Ctrl-X.
- Move to new location.
- Enter
You can start from any other corner and move to the opposite corner, the result will be the same.
Task 2. Jump to the next empty cell.
- Ctrl-Left/Right to jump in a row or Ctrl-Up/Down to jump in a column.
- Release Ctrl.
- Left-Right or Up-Down.
Fill table with formulas with row and column inputs
Tools: absolute and relative references
Level: beginner
Problem
How would you make a multiplication table?
|
|
|
| A | B | C | D | E | F | |
You can type 25 different formulas and refer each to its proper header row and header column. Or you can type a single formula and copy it to the whole table matrix at once.
Solution
Excel will interpret a plain vanilla address, such as A1, as "relative". As you copy it, reference will change by the same number of rows and columns that separate the copied formula from the original cell. It does not matter if you type a cell address, or click on a cell while writing a formula, the result will be the same. To make a formula refer to a fixed cell, row or column, you must create what is called an "absolute" reference.
An absolute reference to a cell looks like this:
=$A$1
Dollar sign works like a "pin" and forces the formula refer to the same row, or column, or both, regardless of where you copy it.
An absolute reference can be either typed directly or created from a relative by a convenient F3 key shortcut. F3 is a circular switch that converts the full relative reference first to full absolute, then to row-absolute, to column-absolute and back to relative reference. So if you accidentally press it more than needed, just keep rolling forward till you get the type of reference you need.
To create a multiplication table,
- Active cell B2.
- Type =B1
- Press F3 two (2) times. B1 will change to $B$1 and then to $B1.
- Type *A2.
- Press F3 three (3) times. A2 will change to $A$2, then to $A2 and then to A$2.
- Press ENTER.
- Copy the formula to all empty cells of the table (both drag-to-copy and any of copy, select and paste methods will work the same).
You now have a table that looks as follows:
| A | B | C | D | E | F | |
| =$A2*B$1 | =$A2*C$1 | =$A2*D$1 | =$A2*E$1 | =$A2*F$1 | ||
| =$A3*B$1 | =$A3*C$1 | =$A3*D$1 | =$A3*E$1 | =$A3*F$1 | ||
| =$A4*B$1 | =$A4*C$1 | =$A4*D$1 | =$A4*E$1 | =$A4*F$1 | ||
| =$A5*B$1 | =$A5*C$1 | =$A5*D$1 | =$A5*E$1 | =$A5*F$1 | ||
| =$A6*B$1 | =$A6*C$1 | =$A6*D$1 | =$A6*E$1 | =$A6*F$1 |
Formula syntax
| Code | Variable | Comment |
| A1 | Full relative reference | After copying, new formulas will refer to a cell in a new column and in a new row |
| $A$1 | Full absolute reference | After copying, new formulas will refer to the same cell |
| A$1 | Relative column, absolute row reference | After copying, new formulas will refer to a cell in the same row but in a new column |
| $A1 | Absolute column, relative row reference | After copying, new formulas will refer to a cell in the same column but in a new row |
Similar tasks
Task 1. Refer a cell to a combination of fixed cell variables, row and column variables.
Make a quote list for a small roofing business. A customer pays you an hourly rate based on roof type, and each roof type requires its specific roofing material that the client also pays for. The state also charges you a sales tax on top of your bill. What will be formula to determine a quote to a customer for an estimated work in hours based on these variables?
| A | B | C | D | E | F | G | |
| Sales tax | 8.5% | Hours >> | |||||
| Roof type | Per hour | Materials | |||||
| Tar paper | |||||||
| Corrugated metal | |||||||
| Ceramic tiles | |||||||
| Wood shingles |
|
|
|
Solution (for cell D1)
=(B$3+(B$4*D$1))*(1+$B$1)
