Techie Help - Excel

I know you guys are jumping for joy – woohoo – it’s excel – finally – it’s about time!  

Excel is one of those programs that you can do a lot in, but most people aren’t aware of just how much they can do.   I’m going to try and cover some of the functions you can use and then some other stuff that I think of.  I’m not sure how well I’ll do this, sometimes I think that excel is one of those that you have to be shown to really understand it.   Also, remember that I’m using Office 2007, so directions I’ll be typing are for that version.  I used to know how to do all this stuff with an older version, but now I can’t remember.  If you can’t figure out, let me know and I’ll see if I can find it online or get access to a pc that has an older version.  I also didn’t include screen shots this time.  If you are interested in trying this, but having trouble and you think the screen shots will help (they always help me) please let me know. I would be happy to email you a copy of this document along with screenshots.

Now for some Random stuff:

First – just to make sure we are clear, excel uses columns and rows.  Rows are the numbers that go down the list (usually located on the left side).  The columns are the letters that go across the top (usually in letters). A cell is the individual square or box that you enter data in.

F2 key – ever type something into a cell and then want to add to it or edit it?  Select the cell, then hit your f2 key, notice that now your curser is after the last character you typed.  You can now make any changes you want. 

Freeze panes

Have you ever been viewing a spreadsheet and scrolled down and then tried to figure out the column heading.  Let’s say you find the row you want and you need the data in a specific column, but you can’t see the column heading anymore and you forgot which letter it is.  What a pain right?   It’s an easy fix though.  You can just freeze the top row (or the first column, or anything else really).  Then when you scroll down the list, that area you “froze” is always visible. I actually do this on all my spreadsheets.  In Office 2007, click on the view tab, then click on freeze panes.  If you want to freeze the top row or the first column, select the option that applies.  If you really want to see the first 5 rows or even more, anything other than just the first one, you can do that too.  Let’s stick with 5. You want to see the first 5 rows whenever you scroll further down the document.  You will want to highlight the 6^th row (you always highlight the row below the last one you want to remain at the top).  You select the row just by clicking on the number, so I want to select row 6, so I click on the 6 on the left side.  Then you click on freeze panes and select the option for “freeze panes” (you would follow the same process for freezing columns).    Let’s say you accidentally freeze the wrong column or row or you just don’t need it anymore, it’s easy to remove.  Click on freeze panes and then unfreeze panes.   Easy right?  I know this was more difficult to do with older versions, just because it was harder to find the freeze pane option (or at least I thought it was). Once you know where it is, great, but finding it on your own can be hard. 

Print titles

Have you ever printed a spreadsheet that takes up more than 1 page?  Ever wish you could see the heading for each column and/or row on each page?  You can! (It would be pretty funny if I said “Too bad, still can’t do that. Haha!).  OK. This is rather easy in Office 2007 as well.  I remember having a hard time finding it in older office versions, but that could also be because I didn’t know what it was called, just what I wanted to do.   So, click on the page layout tab.  Then click on print titles.  This will bring up a page setup dialogue box.  Notice where it says print titles.  Under that it says “rows to repeat at left” and under that it says “columns to repeat at left”.  Obviously, depending on which one you want to do, depends on which one you work with (or both).  I tend to do this with columns more than I do with rows.  So to the right, at the end of that field, there is an icon that has a small red arrow in it.  Click on it.  Notice the dialogue box is now smaller and it says “page setup – columns to repeat at left (obviously that’s the option I selected).  Also notice that your curser now looks like an arrow pointing down.  What you need to do is select the columns you want to repeat at the top.  Obviously you can select all or just specific ones (I always do all that I’m using).  To select the columns just click it with your curser.  If you are selection more than one, click on the first one, hold down the mouse key, then move the mouse to select the rest that you want.  When all are selected, unclick the mouse button.  Now notice that in the page setup – dialogue box it will show the columns you want to repeat (along with some other characters).  Once you have selected all the columns, click on the icon to the left that has the red arrow.  Now you are back at the normal page setup screen but in the columns to repeat field, notice it now shows the columns you wish to repeat.   I will also like to point out that you can manually type in this info, but I find it easier to do the method I described above.   If you are finished, click on “ok” at the bottom.  If you also want to select some rows, just repeat the process for the rows field.

Sort & Filter

This is one that I think a lot of people know, but I do get calls on it once in awhile so thought I would include it.  A custom sort will allow you to sort several columns at once.  For example, I work on a project each month where I have to create a new excel document and sort the info.  I have one column for department name and another for employee name.  I want to sort by department and then employee.  This way I can get all entries for the same department together and then list the users alphabetically (I do this because sometimes the users are entered more than once).  This is very easy to do.   Click on the Home tab, then click on sort & filter, then click on custom sort.  This will bring up the sort dialogue box.  Under column, there is a field for sort by, you need to select which column you want to sort first.  Then in the sort on field you select which ever option works best (I normally select value), then in the order field select the option you want.  Then at the top click on add level.  You will get another row of these same fields.  Fill them in starting with the next column you want to sort by.   Then just fill in the first of the fields and add more levels as you wish.   If you add a level and later decide you don’t want it, there is an option to delete the level.

Now filtering, that’s something I never really had a need to do until recently.  Speaking of the same spreadsheet I mentioned above.  I have to assign co-workers different pc’s to work on. I created a column where I type in the persons’ name who’s assigned to it.  I like to print my list in the morning and that’s what I use to jot down notes and keep track of what I’m doing on these pc’s throughout the day.   So I like to filter the spreadsheet so I only print the ones that I’m interested in.   It’s rather easy to do once you get the hang of it.  On the home tab, click on Sort & Filter, then click on filter.  Notice that there is now an arrow in each column in the first row.  If you click on the arrow for the column you want to filter, it brings up your filter options.  Notice about half-way down or towards the bottom, it will say select all and have a check mark in it, then under that it will have each entry listed.  If you only want one or a couple selected, unselect “select all”.  This will unselect all entries.  Then just select the ones you want to see.   Then click ok.  Now you should be looking at just the entries you want.  To remove the filter,  click on sort & filter, then click on clear.  Or you can click on the arrow again and select all or unselect what was selected and select something else.  You can also filter all or several of the columns too, you don’t have to do just one at a time.  Once you are done with the filter option, just click on sort & filter and then click on filter.

 

Functions

SUM

Sum is one of those functions that gets used a lot, I would guess it’s the most common one.  Sum will add up everything in the columns you select.  You don’t just have to be talking finances either.  If you noticed on my blog, on the right side I like to keep a running tally of the amount of books I’ve read, the genre’s, and the page numbers.  Well..the first year I did it I realized that I sometimes forgot to update that after I reviewed a book and so it wasn’t always correct.   So I created an excel spreadsheet where I actually keep track of each months stats.  I then sum each row up so that at anytime I can look at the total and see where I am at for the year.  (See what happens when a book-a-holic likes computers?).   So let’s say you have a column or a row that you want to add up all the numbers in.  Select the column or row after the last entry you entered in the column/row you want to add.  Type an = sign and then start typing “sum”.  If you have office 2007, you will notice that you get suggestions; you can either type all of sum or use your arrow keys to select it from the list.  Either way, when done click the TAB key.  This will input the entire word (SUM) and put an open parenthesis behind it.  Now, you just select the cells you want to be added together!  If all the cells are together, such as in a row or column, then click on the first cell you want to add, hold down your mouse button and drag it through all the other cells.  When you have all the cells selected, unclick your mouse button and hit enter.  That’s it!  If the cells you want to add aren’t together, no big deal.  Click on the first cell, hold down your ctrl key, then use your mouse to select the rest of the cells.  If any of the cells you select are empty, it will count it as a zero.  If you later enter a number into that cell, it will update the total automatically, you won’t have to do anything else.

Count or counta or countblank

These are functions that I didn’t even know about until last year but have since been able to use several times and love it. 

Count will count the number of cells that you select that contain numbers.  It doesn’t add them up, it just tells you how many of the cells you select have a number entered.

Counta will count all cells that have something in them, so in other words, the cells are not empty.  This is the one I use a lot.  For the spreadsheet I mentioned above, our boss likes to know how many pc’s we need to work on and how many we have completed working on.  The counta function allows my boss to access the spreadsheet at anytime and look at the cell I created for this and know the answers.  So I basically use it as a way to measure our progress.

Countblank does the opposite of counta.  It will count the number of empty cells in your selection.

To use any of these functions, you do the same think you did for SUM.  You access the cell you want this function to be in, then start typing it.  Also, what’s really cool about office 2007 is that if you use the arrow to highlight the selects that office shows, it will tell you want the function is used for.

Ok..so there are a lot of functions out there, but those are the only ones I’m going to cover.  Some are very simple, some are very complicated.  If you have a question about one you want to use but it’s not work, feel free to let me know and I’ll see what I can come up with.  There is a formulas tab that can come in handy.  I personally don’t normally use it, but that’s because I am always using the same formulas and I know what they are.   I have played around with it though and it’s pretty cool.

I just realized this is going to be a rather long post and there’s one more thing I want to cover.

With office 2007 you are given the option to check for errors when using functions.  This is a live saver.  Sometimes you will be using a function, such as SUM, and notice that it’s not correct.  Or you will notice that there is an #ref or something else in a cell.  So you know that somewhere in the spreadsheet this is a mistake.  Now I don’t normally run into this myself but that’s because I’m not using large spreadsheets with lots of functions.  However I do get calls on this at work.   Click on the formula tab.  Click on error checking.  This will just check your entire spreadsheet to see if there are any errors.   Or, if you know you have a field that is wrong, click on it.  Then click on the formula tab.  Then click on either trace precedents or trace dependents.  Depending on which one you select, this will either show you which cells either affect the cell you select or are affected by the cell you selected.   This can really help cut down you having to find the cells and view the data entered.

 

Ok…so long enough, I’m going to stop talking now.  I hope some of this made sense and if it didn’t but you want to know more, I hope you feel comfortable asking me.  There is sooo much more you can do with excel that I didn’t even touch on.  I like excel, I use it a lot, but I don’t use it a lot to where I need to do a ton of fancy smancy stuff.   So, if you have an excel document and want to do something, but not sure how or if it’s possible, feel free to ask that too…I might be able to figure it out.

And if you have read this far..I have just one other request.  What other topics would you like to see me discuss or give tips and tricks on?  I currently don’t have ideas for anything else and welcome any suggestions you might have.