spreadsheets and suitcases

organization + travel = family fun

Travel planning for dorks: spreadsheet tutorial part 6

on November 10, 2014

This is the final post in the spreadsheet tutorial!  See Parts 1, 2, 3, 4, and 5.

Let’s add a bit of functionality to our spreadsheets.  As you have seen, they are not simply a way to list things neatly.  We can use many cool Excel features to make a spreadsheet look good AND do some work for you.

 

The possibilities are endless!

The possibilities are endless!

First, a short glossary and map.

  • cell: one space on the spreadsheet, usually containing one piece of information.  Identified with its column and row position, such as A1, or D15.
  • range: a series of cells, which can be horizontal or vertical.  Many formulas require you to select a range of cells.
  • column: all the cells in a vertical line.  Identified with letters of the alphabet, beginning with A.
  • row: all the cells in a horizontal line.  Identified with ascending numbers, beginning with 1.
  • formula bar: the line used to enter information or a formula into a cell.
  • formula/function: an equation or piece of logic that uses the information in a cell or range of cells.  For mathematical functions, equations will start with the “=” sign, such as =140.20+55.62
  • name box: a space outside of the spreadsheet that identifies which cell you are working in.
  • sheet or “tab”: the collection of cells you are currently working in.  It’s easy to add one, just press the + sign in the circle next to where it says Sheet1.  If you right-click on “Sheet1,” you’ll see options to makes changes to your Sheet/tab, including renaming. You can never have too many!
  • workbook: this is what Microsoft calls the file you are currently working in.  Your workbook contains all your sheets.

map1

 

 

map2

 

Commonly Used Formulas/Functions

  • SUM: this is pretty easy, right?  Just addition.  To add up the values in a range of cells, type =sum(first cell in the range: last cell in the range), such as =sum(C3:C14) as in the example above, which of course equals 365.  To add up values in cells that are spread out around the sheet, just use “+,” such as =B5+C7+F3. 
  • AVERAGE: also self-explanatory.  Write the formula the same way you do for a sum, using a range.  I made an average in the example above (it was in cell D15, written as =AVERAGE(D3:D14).
  • CONDITIONAL FORMATTING: this is a series of formatting options available to a given cell or range of cells.  There are too many options to list here, but you can place specific conditions on a cell so that its background color, text color, or border color changes when those conditions are met.  I use this function to make a cell change color when my Actual Cost for a trip budget item exceeds or falls below my Estimated Cost, as explained in the Budget tutorial post.  Experiment with these- it really helps with household budgets as well…you can see right away when you’ve gone over your budget in any given category.  Well, of course I have a spreadsheet for my household budget!  What kind of spreadsheet dork would I be if I didn’t?  I also have one to track all the books I read in a year…and one to track all the flights my kids have been on…let me stop.

cond formatting

 

Inserting/Using Functions

This is a breeze.  You just have to figure out what you want the values in your cells to tell you.  Press the handy fx button to the left of the formula bar to pull up this dialog box:

function box

You can type the description and Excel will tell you which formula to use, as well as its definition (see the definition for AVERAGE in the picture above).  Under “Or select a category,” if you select All, be prepared for a huge list of words you haven’t seen since high school calculus.  It’s a little scary.  Suffice it to say, Excel is magical and can do any and all math for you.

If you’ve used a formula, gotten the answer, and prefer to change the way that number is presented, click the dropdown menu in the Number Format section.  You can then make your response appear as a monetary amount, a percentage, a fraction, or many more options.  You can modify how your text appears, too, it doesn’t have to be a number.

number optionsNifty shortcuts

  • I often see people scrolling through large spreadsheets looking for information.  Please, save your eyes and use “Ctrl + F.” Find anything in the current sheet or workbook easily.  You can also Find and Replace, if you realize after the fact that you had misspelled something in the first sheet and copied it in many other places.

find

 

  • If you have this problem: long titleYou can solve it at least 3 different ways: First, you can extend the size of the column (Column A, in this case) to the exact length of your text by holding your mouse on the line between Column A and Column B until you see what looks like a plus sign with arrows on the east and west points, then double-clicking; this sometimes messes up the formatting of the cells below.  If you no likey that (I definitely don’t), just hit Undo and turn to my good friends in the Alignment section, Wrap Text and Merge & Center.

wrap and merge center

          Wrap Text resizes the cell to fit the information in it:

wrap text

          Merge & Center combines adjacent cells and centers the text.  Just select at least 2 cells.  The merged cells then act as one.  Voilà!

merge & center

  • I get annoyed when scrolling and lose my header columns or row names.  Enter the Freeze Panes options.  First, at the top, change your menu from Home to View.  Then select Freeze Panes in the Window Section.  You’ll see the options below.  Use Freeze Panes for a custom view, or just use Freeze Top Row and Freeze First Column.  You can use both Top Row and First Column at the same time.  Now, you can scroll having to go back to the top to see what you are looking at.

freeze

  • You’ll also find the Hide function in the Window Section of the View menu.  Hide allows you to hide a column, row or even a whole workbook.  This allows you to keep the information available, but hide away if you don’t need to see it just then.  I find this very useful at work- they send me spreadsheets will all sorts of information that doesn’t help me.  Instead of deleting the column, I just right-click on the column letter, and select Hide.  It reduces the clutter on my screen, and I can always get the column back by hovering my mouse over where the column used to be, and extending the size again.  Many a work crisis has been solved by looking carefully at a sheet and realizing that info has not disappeared, it was just hidden.  You can tell because a letter will be skipped- if I hid Column C, my screen will show columns in the following order: A, B, D, etc.
  • Finally, Insert Comment is great for, well, inserting comments.  You right-click on a cell, and type in a little note.  I like to remind myself of the hours for a tourist attraction, to remind my husband what credit card to use for a type of expense, or leave funny notes to myself to find later.  I’m easily amused.  A cell with a comment attached will display a little red triangle in the top right corner.  Hover your mouse over the triangle to see the comment:

comment

Whew!  Look, if all else fails, just use this Microsoft Tutorial for Excel.  Don’t forget to play with the fonts, text color, and borders to make it look pretty as well.  Happy spreadsheeting!

Advertisements

One response to “Travel planning for dorks: spreadsheet tutorial part 6

  1. gina says:

    thanks for the super fast and easy way to work the most useful functions of Excel.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: