Alright, now we're getting into the good stuff: formulas!
In Word©, combining two pieces of information is simple: you cut/copy and paste them together. (It's even simpler with the aforementioned shortcuts, just sayin'). However, in Excel©, it is not that simple. Yes, you can just copy and paste, but simple copying and pasting only works if you're copying whole cell and replacing another whole cell with it. In order to actually combine two cells into one, you have to edit the first cell, select the text, copy it, edit the second cell, paste it, etc. If you enjoy menial and boring work, you can do it this way. If you're like me, you don't like double clicking and scrolling and, and stuff! Well, concatenate is the answer to your troubles!
Concatenate is a formula that allows you to combine the text from two cells into a whole new cell, right next to each other. Example: if you had a list of first and last names, but you wanted full names and the list was upwards of 200 people long, this formula will have them combined into full names before you can say "supercalifragilisticexpialidocious" (I spelled that right on the first try! Woot! Sorry, back to the lesson...) Anyway, before you can say that really long word (and possibly you might have to sing the song), you'll be able to write a concatenate formula for every single entry in your 200+ list. Maybe not on your first try, but after awhile, you'll be able to do it three seconds. (If you can say "supercalifragilisticexpialidocious" in under three seconds...get a hobby).
Interesting tidbit: concatenate comes from the Latin word concatēnāre which means "to link together". And this is what you're telling Excel© to do.
Anyway, on to the formula! You ready? Too bad.
This is what the formula looks like in the cell: =concatenate(A1,B1,C1)
Important: If you are new to formulas, all formulas must start with an "=" or Excel© will not recognize them as a formula, and you'll just end up typing text and it won't help you at all.
Now, let's break down this formula:
= This tells Excel© you're entering a formula
concatenate This is the formula name. It tells Excel© what you want it to do, in this case, combine two are more cells into one.
(A1,B1,C1) This is where you put what you want to combine. In this example, you are combining three cells. I'll show you how to enter text in a minute.
If A1 = "Fred"
B1 = "M."
C1 = "Julian"
This formula would give you: "FredM.Julian"
Now, as you can see, there aren't spaces inherent in the formula. Excel© may be incredibly smart and amazing, but it doesn't know where to put spaces unless you tell it to. Which brings us to inputting text in the formula...
To put text in the formula, simply put it in quotes.
Ex: =concatenate(A1," ",C2)
Quotations in formulas tell Excel© that you want it to print what falls between the quotation marks. Say you wanted to print "Julian, Fred" (using the info above), your formula would look like this:
=concatenate(C1,", ",A1)
or
=concatenate(C1,","," ",A1)
The second example is the same as the first, except that I separated the comma and space. You can do it either way. The second way gets confusing with longer formulas, but I kinda like it for short ones.
A few other things you should know:
1. You must put the formula into a new cell. It's easiest if you insert a new column next to the columns you wish to combine and just combine across.
2. You can select a cell instead of typing A1 or B1 every time. Excel© will input the correct info for whatever cell you select. You can even use the arrow keys to select it. When you hit the comma, the selection tool will return to the formula cell (though the cell you selected should be highlighted in blue or something) and you can move it from there to select the next piece of info.
Commas in Excel© formulas denote the end of a section. The selection tool resets to the active cell after each comma. In OpenOffice©, a semi-colon replaces the comma.
3. Once a formula is written, you can copy/paste it down the whole column. It will change the cell references automatically. (**See Lesson 2: Absolutes)
4. You can concatenate any number of cells from anywhere on the spreadsheet. However, if you copy/paste that formula, you must remember that cell references change in relationship to the formula cell. (see Lesson 2: Absolutes). Besides, it's easier to understand and remember what you're trying to do if it's organized and you just have to read it across the row.
One final note: Before you delete or change any of the reference cells (the cells you combined), you must copy/paste the formulas as values, or they will automatically change when you change the cells, and if you move anything, Excel© goes kinda berserk. To paste it as values, copy the column and go to paste special (either a shortkey, or it's under the "Home" tab) If you use the shortcut, it brings up a dialogue box; check "values only" and hit "ok". If you use the "Home" tab, under the clipboard picture "Paste", click the arrow for more options, and there is a "Paste Values" option. What paste values does is it changes the formulas into text. If you click on the cell before repasting it, the formula bar (the display bar at the top) displays the formula you typed, not the answer. By pasting values, you get rid of the formula, but keep the answer, so it will display the actual text. Once done, you can delete, move, cut, edit or do whatever else you want to the original data (in our example, the first and last names) and the full name will stay the same.
If you have any questions, concerns, or comments, post a comment and I'll answer it as soon as I can. Also, if it's urgent and you understand what I've said here, you'd be fine searching on Google© for "excel 2007 your question" and you'll get some answers that way.
Tuesday, September 7, 2010
Lesson 2: Absolutes
Another fun pre-formula lesson on confusing/interesting things in Excel©! This time, stick with me, because we're talking about formula things without formulas...so here we go!
Absolutes are formula-language for "stay". Basically what you're telling Excel© to do is keep the reference cell where you told it the first time.
When you type a formula, most, if not all the time, it will automatically input the cell as a "relationship" instead of a "cell". (Those aren't the technical terms). Basically what it does is it will display in the formula "A1" but what Excel© thinks is "the cell two to the left of the formula cell". So, when you move the formula to row B, directly under the original, it will change the reference to "B1" because that is the cell two to the left of the formula cell. And if you move it to G5, it displays "G3". Ok?
So this is fairly useful for most formulas when you need to copy paste one formula down and have it apply to a whole column of info, but it gets annoying if, say, you're calculating a percent and you don't want the "total" of something to change every time. The answer to this dilemma is to tell Excel© to "stay". This is signified by a $ before the variable you want to keep the same. This $ must appear before every variable you want the same.
Example:
$B1 when copied to cell S5 will become $B5, because you told it that you want it to keep referencing column B, but the row is changing.
B$1 when copied to cell S5 will become S$1, because you told it that the column is changing, but the row is staying the same.
$B$1 when copied to any other cell will stay $B$1, because you told it you wanted it to stay where you put it.
(Here comes an analogy, because I am an analogy person)
It's like making a rabbit run. If you want the rabbit to stay in the left side of the pen, you fence off that side of the pen. If you want the rabbit to stay in the front of the pen, you fence off the front. If you want the rabbit to stay in the top-left corner, you put fence around that corner. When you move the food bowl, the rabbit will get as close to the food bowl as it can, within the fencing parameters you set.
Now, instead of having to retype the automatic "freelance" input Excel© does with dollar signs, there is a simpler way. Right after you select a cell, hit F4. This cycles through all the different absolute possibilities. Hit F4 until the $ signs are in the correct combination. If you forget to do it as you type, like I forget all the time, all you have to do is edit the formula, put the cursor either before or in the middle of the cell reference and hit F4 until your absolutes are displayed.
Note: If you've already copied the formula with the un-absoluted reference, you will have to re-copy it. Excel© does not automatically change all the formulas. Once a formula is copy/pasted from the original, Excel© treats it as it's own formula, and won't associate it with any other parent or child formula you copy/paste.
That's basically it. Absolutes are most valuable in mathematical formulas, such as percents, where one variable is constant, and in things like vlookups, where the range stays constant (We'll see these later).
Note: (I like notes) Some formulas will automatically input the reference as an absolute. Example: if in a vlookup, you select the whole spreadsheet instead of a range of cells, it will automatically input that as a absolute. A general rule of thumb is to assume Excel© won't make it absolute (like a dog won't naturally stay) so you'll just have to check each time and decide whether or not you need the absolute.
Absolutes are formula-language for "stay". Basically what you're telling Excel© to do is keep the reference cell where you told it the first time.
When you type a formula, most, if not all the time, it will automatically input the cell as a "relationship" instead of a "cell". (Those aren't the technical terms). Basically what it does is it will display in the formula "A1" but what Excel© thinks is "the cell two to the left of the formula cell". So, when you move the formula to row B, directly under the original, it will change the reference to "B1" because that is the cell two to the left of the formula cell. And if you move it to G5, it displays "G3". Ok?
So this is fairly useful for most formulas when you need to copy paste one formula down and have it apply to a whole column of info, but it gets annoying if, say, you're calculating a percent and you don't want the "total" of something to change every time. The answer to this dilemma is to tell Excel© to "stay". This is signified by a $ before the variable you want to keep the same. This $ must appear before every variable you want the same.
Example:
$B1 when copied to cell S5 will become $B5, because you told it that you want it to keep referencing column B, but the row is changing.
B$1 when copied to cell S5 will become S$1, because you told it that the column is changing, but the row is staying the same.
$B$1 when copied to any other cell will stay $B$1, because you told it you wanted it to stay where you put it.
(Here comes an analogy, because I am an analogy person)
It's like making a rabbit run. If you want the rabbit to stay in the left side of the pen, you fence off that side of the pen. If you want the rabbit to stay in the front of the pen, you fence off the front. If you want the rabbit to stay in the top-left corner, you put fence around that corner. When you move the food bowl, the rabbit will get as close to the food bowl as it can, within the fencing parameters you set.
Now, instead of having to retype the automatic "freelance" input Excel© does with dollar signs, there is a simpler way. Right after you select a cell, hit F4. This cycles through all the different absolute possibilities. Hit F4 until the $ signs are in the correct combination. If you forget to do it as you type, like I forget all the time, all you have to do is edit the formula, put the cursor either before or in the middle of the cell reference and hit F4 until your absolutes are displayed.
Note: If you've already copied the formula with the un-absoluted reference, you will have to re-copy it. Excel© does not automatically change all the formulas. Once a formula is copy/pasted from the original, Excel© treats it as it's own formula, and won't associate it with any other parent or child formula you copy/paste.
That's basically it. Absolutes are most valuable in mathematical formulas, such as percents, where one variable is constant, and in things like vlookups, where the range stays constant (We'll see these later).
Note: (I like notes) Some formulas will automatically input the reference as an absolute. Example: if in a vlookup, you select the whole spreadsheet instead of a range of cells, it will automatically input that as a absolute. A general rule of thumb is to assume Excel© won't make it absolute (like a dog won't naturally stay) so you'll just have to check each time and decide whether or not you need the absolute.
Saturday, September 4, 2010
Lesson One: Shortcuts
I don't know about you, but I get impatient and bored very easily. I find tedious work to be, well, tedious. Scrolling is one of those tedious tasks I find annoying sometimes. I mean really, it takes so much energy to move your hand from the keyboard to the mouse and back again every time you need to scroll. And let's face it, most of us don't have time to sit and wait for the little selection box in Excel© to scroll all the way to the bottom of that 5000 piece list! There has to be a more time and energy saving way!
Well, there is. Even though the above situation has been a bit over-exaggerated, there are times when messing around with huge Excel© documents gets time-consuming. Luckily, Microsoft™ has already programmed many shortcuts into their software. Many of these shortcuts also work for OpenOffice© but I cannot say for sure.
If you look online, you can find many full lists of all the shortcuts your heart could ever begin to desire. I found one such list and use it every time I'm editing an Excel© document. Here I have taken this list and expanded on it to hopefully make it easier to read and understand, for it can be a little tech-savvy sometimes. This list contains only those things I think practical to use. There are over two hundred shortcuts on the list, so if you really wish to use them, the link to the full list is at the bottom of this post:
f1 - Help
ctrl+p - Print
ctrl+f2 - Print Preview
ctrl+s - Save
f12 - Save as
ctrl+- - Delete
ctrl+f - Find
ctrl+h - Find and Replace
ctrl+alt+v - Display the Paste Special dialog box. Useful when making formulas into values
ctrl+v - Paste
ctrl+x - Cut the selected cells.
ctrl+z - Undo
ctrl+y - Redo
ctrl+c - Copy selected cells
ctrl+b - Apply or remove bold formatting.
ctrl+i - Apply or remove italic formatting.
ctrl+u - Apply or remove underlining.
ctrl+shift+: - Enter the current time.
ctrl+; - Enter the current date.
ctrl+shift++ - Insert
ctrl + arrow keys - Move to the edge of the current data region AKA if the cell is blank, it goes to the next non-blank cell; if it's non-blank, it goes to the next blank cell **Keep in mind that cells that look blank may not be blank if they have some formatting in them**
ctrl+end - Move to the last cell on a worksheet, in the lowest used row of the rightmost used column. If the cursor is in the formula bar, CTRL+END moves the cursor to the end of the text
home - Move to the beginning of a row
ctrl+home - Move to the beginning of a worksheet. (Cell A1)
alt+page down - Move one screen to the right in a worksheet.
alt+page up - Move one screen to the left in a worksheet.
ctrl+page down - Move to the next sheet in a workbook.
ctrl+page up - Move to the previous sheet in a workbook.
f4 - This is used for Functions to cycle through "absolutes". Absolutes are covered in a later post on functions.
ctrl+shift+" - Copies the value from the cell above the active cell into the active cell.
ctrl+d - Uses the Fill Down command to copy the contents and formats of the topmost cell of a selected range into the cells below.
alt+shift+f1 - Insert a new worksheet.
f2 - Edit the active cell.
ctrl+n - Create a new, blank workbook (this is a whole new document)
ctrl+spacebar - Select an entire column in a worksheet.
shift+spacebar - Select an entire row in a worksheet.
ctrl+a - Select the entire data range and a second time selects the entire worksheet.
shift+f3 - Insert a Function
alt+f8 - Display the Macro dialog box to create, run, edit, or delete a macro. **An introduction to Macros will be posted later.**
Many get frustrated when they want to create a new line in a cell, and the stupid thing exits the cell when they hit enter. Then they must retype everything back in and they still have the same road-block. Well, the Alt key is the answer. Alt + Enter allows you to enter inside a cell without exiting the editor. So next time you try to merge two cells in order to get that enter, just hit the Alt key.
I think that is enough of a deluge of information for now. Tune in next time for more ways to confuse yourself with in Microsoft™ Excel©.
**Thanks to shortcutworld.com for the original shortcut list, found at http://www.shortcutworld.com/en/win/Excel_2007.html.**
Well, there is. Even though the above situation has been a bit over-exaggerated, there are times when messing around with huge Excel© documents gets time-consuming. Luckily, Microsoft™ has already programmed many shortcuts into their software. Many of these shortcuts also work for OpenOffice© but I cannot say for sure.
If you look online, you can find many full lists of all the shortcuts your heart could ever begin to desire. I found one such list and use it every time I'm editing an Excel© document. Here I have taken this list and expanded on it to hopefully make it easier to read and understand, for it can be a little tech-savvy sometimes. This list contains only those things I think practical to use. There are over two hundred shortcuts on the list, so if you really wish to use them, the link to the full list is at the bottom of this post:
f1 - Help
ctrl+p - Print
ctrl+f2 - Print Preview
ctrl+s - Save
f12 - Save as
ctrl+- - Delete
ctrl+f - Find
ctrl+h - Find and Replace
ctrl+alt+v - Display the Paste Special dialog box. Useful when making formulas into values
ctrl+v - Paste
ctrl+x - Cut the selected cells.
ctrl+z - Undo
ctrl+y - Redo
ctrl+c - Copy selected cells
ctrl+b - Apply or remove bold formatting.
ctrl+i - Apply or remove italic formatting.
ctrl+u - Apply or remove underlining.
ctrl+shift+: - Enter the current time.
ctrl+; - Enter the current date.
ctrl+shift++ - Insert
ctrl + arrow keys - Move to the edge of the current data region AKA if the cell is blank, it goes to the next non-blank cell; if it's non-blank, it goes to the next blank cell **Keep in mind that cells that look blank may not be blank if they have some formatting in them**
ctrl+end - Move to the last cell on a worksheet, in the lowest used row of the rightmost used column. If the cursor is in the formula bar, CTRL+END moves the cursor to the end of the text
home - Move to the beginning of a row
ctrl+home - Move to the beginning of a worksheet. (Cell A1)
alt+page down - Move one screen to the right in a worksheet.
alt+page up - Move one screen to the left in a worksheet.
ctrl+page down - Move to the next sheet in a workbook.
ctrl+page up - Move to the previous sheet in a workbook.
f4 - This is used for Functions to cycle through "absolutes". Absolutes are covered in a later post on functions.
ctrl+shift+" - Copies the value from the cell above the active cell into the active cell.
ctrl+d - Uses the Fill Down command to copy the contents and formats of the topmost cell of a selected range into the cells below.
alt+shift+f1 - Insert a new worksheet.
f2 - Edit the active cell.
ctrl+n - Create a new, blank workbook (this is a whole new document)
ctrl+spacebar - Select an entire column in a worksheet.
shift+spacebar - Select an entire row in a worksheet.
ctrl+a - Select the entire data range and a second time selects the entire worksheet.
shift+f3 - Insert a Function
alt+f8 - Display the Macro dialog box to create, run, edit, or delete a macro. **An introduction to Macros will be posted later.**
Many get frustrated when they want to create a new line in a cell, and the stupid thing exits the cell when they hit enter. Then they must retype everything back in and they still have the same road-block. Well, the Alt key is the answer. Alt + Enter allows you to enter inside a cell without exiting the editor. So next time you try to merge two cells in order to get that enter, just hit the Alt key.
I think that is enough of a deluge of information for now. Tune in next time for more ways to confuse yourself with in Microsoft™ Excel©.
**Thanks to shortcutworld.com for the original shortcut list, found at http://www.shortcutworld.com/en/win/Excel_2007.html.**
Thursday, September 2, 2010
Simply Wonderful
Here are some easy shortkeys that I at least will be using quite a bit in these blogs:
These next ones are called "character entities". Basically they are symbols not accessible in on the keyboard. In Word©, they are under "insert symbol". For Web-based pages where this menu is unavailable, though, these codes can be easily entered to add the symbol. They must be entered while editing the HTML coding, not text. For most commercial postings, like Blogger, there is an "edit HTML" button or option somewhere in the editor, quite easily accessible.
Copyright (©) - type © or ©
Registered Trademark (as in a company) (®) - type ® or ®
Trademark (something with a pending trademark registration) (™) - ™ or ™
*Note: The code containing the name may not be supported on some web services. If they do not work, try the code containing the number. Most web services will recognize the number even if they don't recognize the name.
**Also: If you try the code and it doesn't work with either name or number, make sure you don't forget the semi-colon at the end. If this doesn't work, I don't know what to tell you; that's one of those questions to pose to a computer-savvy person, or search for it online.
If you have need of any other entities, you can easily find complete lists online.
One function I will be using quite a bit, and something I find truly helpful everyday is called "print screen". When I was in high school, I thought you had to have a camera program to take a picture of the computer screen. I spent late hours, long into the night on my Dad's computer because his was the only one in our house with a screenshot camera. If only I had known that most, if not all keyboards these days come with a "printscreen" button! This button is generally found in the upper right-hand corner of the keyboard, and is either a separate button (as with desktop keyboards) or a function (Fn) key (as with laptops). Function keys are accessed by holding down the Fn key (bottom left-hand corner) and pressing the appropriate main key (in my case, it's the "end" button that doubles as "printscreen").
This button is a fantastic little feature that takes a picture of your screen just as you see it (minus the pointer, which it erases for some reason). It saves the screen as a picture and places it on your clipboard. All you have to do is go into Word© or PowerPoint© and paste it into a document (Ctrl v). Then you can edit it with the normal Microsoft® picture editing functions. To use it, right-click it and save it as a picture (you may need to copy/paste it into PowerPoint© in order to save it as a picture. I sometimes have trouble doing that in Word.) You can then treat it like any, normal picture in your library. I truly use this feature at least once a week, if not more often (and that was before I started this blog).
As of now, that's all the handy shortcuts I can think of. When I think of more, I'll post them. I will also post a comprehensive list of all the Excel shortkeys (which I use regularly) that I know of.
Check back for more to come!
These next ones are called "character entities". Basically they are symbols not accessible in on the keyboard. In Word©, they are under "insert symbol". For Web-based pages where this menu is unavailable, though, these codes can be easily entered to add the symbol. They must be entered while editing the HTML coding, not text. For most commercial postings, like Blogger, there is an "edit HTML" button or option somewhere in the editor, quite easily accessible.
Copyright (©) - type © or ©
Registered Trademark (as in a company) (®) - type ® or ®
Trademark (something with a pending trademark registration) (™) - ™ or ™
*Note: The code containing the name may not be supported on some web services. If they do not work, try the code containing the number. Most web services will recognize the number even if they don't recognize the name.
**Also: If you try the code and it doesn't work with either name or number, make sure you don't forget the semi-colon at the end. If this doesn't work, I don't know what to tell you; that's one of those questions to pose to a computer-savvy person, or search for it online.
If you have need of any other entities, you can easily find complete lists online.
One function I will be using quite a bit, and something I find truly helpful everyday is called "print screen". When I was in high school, I thought you had to have a camera program to take a picture of the computer screen. I spent late hours, long into the night on my Dad's computer because his was the only one in our house with a screenshot camera. If only I had known that most, if not all keyboards these days come with a "printscreen" button! This button is generally found in the upper right-hand corner of the keyboard, and is either a separate button (as with desktop keyboards) or a function (Fn) key (as with laptops). Function keys are accessed by holding down the Fn key (bottom left-hand corner) and pressing the appropriate main key (in my case, it's the "end" button that doubles as "printscreen").
This button is a fantastic little feature that takes a picture of your screen just as you see it (minus the pointer, which it erases for some reason). It saves the screen as a picture and places it on your clipboard. All you have to do is go into Word© or PowerPoint© and paste it into a document (Ctrl v). Then you can edit it with the normal Microsoft® picture editing functions. To use it, right-click it and save it as a picture (you may need to copy/paste it into PowerPoint© in order to save it as a picture. I sometimes have trouble doing that in Word.) You can then treat it like any, normal picture in your library. I truly use this feature at least once a week, if not more often (and that was before I started this blog).
As of now, that's all the handy shortcuts I can think of. When I think of more, I'll post them. I will also post a comprehensive list of all the Excel shortkeys (which I use regularly) that I know of.
Check back for more to come!
Wednesday, September 1, 2010
The Low-Down on the New Excel©
Actually, contrary to what the title suggests, I know very little about why the developers of Microsoft® changed Excel© with this newer version. I do know, however, that switching from one to the other is quite confusing, seeing that basically everything is in a different spot. This is not a bad thing, but it does make it somewhat difficult to work quickly and efficiently.
I spent the summer after high school trying to figure out how to use several of the Excel© formulas. I found that, while Excel© has a help index, it was fairly confusing to computer illiterate teen. Luckily, many kind professors and others have taken the time to post directions on how to use Excel© formulas in various places across the Internet. The difficulty I found in searching through these, though, was that often times one person would do one formula, another would detail a different one, and very few sights had a complete, comprehensive listing of how to use formulas. There are a few out there, but they didn't have as much detail as I needed. This blog may obviously face the same problems, but I hope it will be helpful. I will not be explaining why everything works, but how to use it. If you would like to know why it works, one of the professional tech-savvy sights that pops up when you search the Internet for it will tell you all you could ever want to know. I mean for this blog to help those like I was as a high school senior, new to Excel© and completely at a loss for where to start.
I spent the summer after high school trying to figure out how to use several of the Excel© formulas. I found that, while Excel© has a help index, it was fairly confusing to computer illiterate teen. Luckily, many kind professors and others have taken the time to post directions on how to use Excel© formulas in various places across the Internet. The difficulty I found in searching through these, though, was that often times one person would do one formula, another would detail a different one, and very few sights had a complete, comprehensive listing of how to use formulas. There are a few out there, but they didn't have as much detail as I needed. This blog may obviously face the same problems, but I hope it will be helpful. I will not be explaining why everything works, but how to use it. If you would like to know why it works, one of the professional tech-savvy sights that pops up when you search the Internet for it will tell you all you could ever want to know. I mean for this blog to help those like I was as a high school senior, new to Excel© and completely at a loss for where to start.
Subscribe to:
Posts (Atom)