Discussion on:
View:
Show:
Are you familiar with all the techniques covered here? (Are your Excel users?) What other timesavers would you add to the list?
Some of these time-savers are known to most Excel users; in fact, I realized after reading this article that I have been unconsciously using most of these for years. But it is good to find them listed in one place for easy reference.
The data menu in excel is actually very helpful for different types of data manipulation, particularly filtering, sorting and validation. For those who chose to install the optional Analysis Toolpack with the main program, the previous menu 'Tools' also contains data analysis options that can do basic to moderately advanced statistical analysis right on the worksheet.
The Control key is very important for fast operation, particularly when used in combination with the mouse. Copying worksheets is a snap when pressing the control key and dragging (with the mouse cursor) the worksheet name just to the next position; it creates a copy with the same name and a numbered suffix. The control key can also be used to select multiple worksheets (press control and click on the names of all the sheets; selected sheets have a lighter colored tab), all of which can then be deleted (by pressing delete) or copied (by just dragging one position to the left or right).
The data menu in excel is actually very helpful for different types of data manipulation, particularly filtering, sorting and validation. For those who chose to install the optional Analysis Toolpack with the main program, the previous menu 'Tools' also contains data analysis options that can do basic to moderately advanced statistical analysis right on the worksheet.
The Control key is very important for fast operation, particularly when used in combination with the mouse. Copying worksheets is a snap when pressing the control key and dragging (with the mouse cursor) the worksheet name just to the next position; it creates a copy with the same name and a numbered suffix. The control key can also be used to select multiple worksheets (press control and click on the names of all the sheets; selected sheets have a lighter colored tab), all of which can then be deleted (by pressing delete) or copied (by just dragging one position to the left or right).
I use tip #10 so often that I made it a macro... Ctrl-Shift-V. Invaluable!!
This was a great set of tips, Jody! How about giving us another 10 like these in February.
This was a great set of tips, Jody! How about giving us another 10 like these in February.
Ditto - but instead of a macro, I mod the menu with the Paste Values icon: a clipboard with 12 on it.
Mac
Mac
I needed a list of excel tricks for our users that they could understand and use. You provided that for me. Thank you so much for the article. I look forward to more.
This is something I show users all the time:
Text to Columns
Do you need to split out First/Last Names from one colum to 2 columns? Follow these steps:
Make sure you have at least 2 blank columns after the column you want to split out. If you have middle initials or suffixes, you want to make sure you have several blank columns.
Okay, select the column you want to break up.
Choose Data>Text to columns
Choose Delimited, Next
Choose Delimiter type (mine is usually a space or Comma), Choose Next
You can choose your data format for each new column, or just choose finish.
Voila! Your Column that contained the first and last name, is now split into 2 columns. (if you had suffixes and middle initials, you will need to rearrange things a bit, but this should help you out with most of the work!)
Oh, on the reverse side...if you want to combine columns you can create a formula to join (concatinate) the two columns together.
ex: A1=fname, B1=Lname, I want C1=Last, First
formula in C1: =B1&", "A1
copy this formula down to the last row of names. Then if you want the data to be static, use the Paste Special Values to Change the formula to values!!
Hope this helps!
Text to Columns
Do you need to split out First/Last Names from one colum to 2 columns? Follow these steps:
Make sure you have at least 2 blank columns after the column you want to split out. If you have middle initials or suffixes, you want to make sure you have several blank columns.
Okay, select the column you want to break up.
Choose Data>Text to columns
Choose Delimited, Next
Choose Delimiter type (mine is usually a space or Comma), Choose Next
You can choose your data format for each new column, or just choose finish.
Voila! Your Column that contained the first and last name, is now split into 2 columns. (if you had suffixes and middle initials, you will need to rearrange things a bit, but this should help you out with most of the work!)
Oh, on the reverse side...if you want to combine columns you can create a formula to join (concatinate) the two columns together.
ex: A1=fname, B1=Lname, I want C1=Last, First
formula in C1: =B1&", "A1
copy this formula down to the last row of names. Then if you want the data to be static, use the Paste Special Values to Change the formula to values!!
Hope this helps!
I use this feature a lot. I usually want to format the resulting columns as text. Does anybody know how to make text the default format for this feature?
I don't know how to make it default to text, but on step 3 of the text to columns wizard you can click on a column and then in the upper right corner you can select; General, Text, Date, or "Do not import". Which one you select will determine the format for that column or even if the column will exist when you are done. I have used this often for a column I get from our cafeteria and our deli. The column reads cafe1, cafe2, or Deli1, Deli2 etc; where the number indicates which register was used at that location.. I do not need the number so I do a text to columns and then check the do not import box for the column that contains the number. This way I can dump it into access with just cafe or deli showing and then run reports based on the location without regard to which register it was rung up on. I suppose I could have written code to figure it out as well but this was quick and simple.
Well done.
I spend a great deal of time working on a notebook computer and routinely use Ctrl+ features that would probably not be considered obscure, but are my biggest time savers, every day.
I use the Ctrl+C for copy, and Ctrl+V for paste. Only when I have to Paste Special do I bother with the pull down menus. It seems small, but the cumulative time savings is huge. Once you learn the keystrokes so that you don't have to consciously choose them, you'll never go back.
I occassionally am recording and editing work while leading a group discussion, and this feature helps keep things moving, and keeps others from spending time watching me navigate menus, and focused on the content.
I spend a great deal of time working on a notebook computer and routinely use Ctrl+ features that would probably not be considered obscure, but are my biggest time savers, every day.
I use the Ctrl+C for copy, and Ctrl+V for paste. Only when I have to Paste Special do I bother with the pull down menus. It seems small, but the cumulative time savings is huge. Once you learn the keystrokes so that you don't have to consciously choose them, you'll never go back.
I occassionally am recording and editing work while leading a group discussion, and this feature helps keep things moving, and keeps others from spending time watching me navigate menus, and focused on the content.
Ctrl+D copies cell above
Ctrl+S saves workbook
Ctrl+Z undo
Ctrl+Tab switch between workbooks
Ctrl+Shift+Down Arrow key select contiguous cells
Ctrl+Shift+8 select contiguous range
Ctrl+Any Arrow key takes you to the last used cell in a range
If you really want to learn about Excel I suggest MrExcel.com They have a wonderful forum there. Bill Jelen also has a daily podcast on Excel.
Ctrl+S saves workbook
Ctrl+Z undo
Ctrl+Tab switch between workbooks
Ctrl+Shift+Down Arrow key select contiguous cells
Ctrl+Shift+8 select contiguous range
Ctrl+Any Arrow key takes you to the last used cell in a range
If you really want to learn about Excel I suggest MrExcel.com They have a wonderful forum there. Bill Jelen also has a daily podcast on Excel.
1. I would add dynamic named ranges. This is one of the most useful things you can learn to do by far.
2. You might want to type within a range, but get tired of manually selecting the first cell in the row every time. Try selecting the entire range. Then start typing! Just press enter or tab to move to the new cell.
3. A dependent validation list can be very useful, and not many people know how to make them
4. Too many people merge cells. This is a terrible thing to do in many cases. If you wish to center some text across several cells try this: Type the words you want into a cell and then select the cells you wish for it to be centered across. Go to Format>Cells Alignment with horizontal set to Center across selection.
5. Alt+= will sum a range.
6. Double click a cell border in a used range when the cursor is four arrows. This will take you to the last used cell in a range. This works on all sides of a cell.
7. How many days between two dates? "12/8/2007"-"12/3/2007" with the quotes.
8. You know how to move a selected range of cells around. But if you try to drop those cells it will overwrite any data underneath. So if you want to drop that range in between two columns try holding down the Shift key. You will now see a line between the two columns. Now you can insert the data.
9. Dragging a range using the right mouse button gives you a menu that most people don't know exist.
10. If you use named ranges and want to see were they are, or their names then try reducing your zoom to 39%. You have to try this if you do.
11. A lot of people know this, but I would hate to think that somebody didn't know they could double click the corner of a cell with a formula to fill it down as many cells as there are in the column to the left.
12. Highlight parts of a formula and press F9 to see what that part equates to. Great for trouble shooting a formula, or for making arrays.
13. Alt+Enter is like a carriage return on a typewriter.
14. Make a reference to a cell address absolute or relative by highlighting the reference in a formula and pressing F4.
2. You might want to type within a range, but get tired of manually selecting the first cell in the row every time. Try selecting the entire range. Then start typing! Just press enter or tab to move to the new cell.
3. A dependent validation list can be very useful, and not many people know how to make them
4. Too many people merge cells. This is a terrible thing to do in many cases. If you wish to center some text across several cells try this: Type the words you want into a cell and then select the cells you wish for it to be centered across. Go to Format>Cells Alignment with horizontal set to Center across selection.
5. Alt+= will sum a range.
6. Double click a cell border in a used range when the cursor is four arrows. This will take you to the last used cell in a range. This works on all sides of a cell.
7. How many days between two dates? "12/8/2007"-"12/3/2007" with the quotes.
8. You know how to move a selected range of cells around. But if you try to drop those cells it will overwrite any data underneath. So if you want to drop that range in between two columns try holding down the Shift key. You will now see a line between the two columns. Now you can insert the data.
9. Dragging a range using the right mouse button gives you a menu that most people don't know exist.
10. If you use named ranges and want to see were they are, or their names then try reducing your zoom to 39%. You have to try this if you do.
11. A lot of people know this, but I would hate to think that somebody didn't know they could double click the corner of a cell with a formula to fill it down as many cells as there are in the column to the left.
12. Highlight parts of a formula and press F9 to see what that part equates to. Great for trouble shooting a formula, or for making arrays.
13. Alt+Enter is like a carriage return on a typewriter.
14. Make a reference to a cell address absolute or relative by highlighting the reference in a formula and pressing F4.
Nice list. Just a few notes:
Tip 1:
"There are, of course, other ways to select all the cells in a worksheet. If you're a keyboard person, press [CTRL]A. If you're a menu person, go to Edit | Select All."
In 2003 there is no Select All in the Edit menu, but [CTRL]A works. 2007 does, however, have the Select All menu item. [CTRL]A is also a great way to select a contiguos range of non-empty cells (such as a table). Just select a cell in the range and follow that with [CTRL]A.
Tip 6:
It can become a mess if not done carefully, but subtotals can be nested or layered if care is taken in selecting the cells to apply the subtotal to. After applying subtotals, if one's desire is to redefine the current subtotals, choose Data | Subtotals and redefine leaving Replace Current Subtotals checked. If your desire is to add additional subtotals, uncheck Replace Current Subtotals and proceed.
Tip 7:
Autocalc will display any one of the available calculations for 2003. For 2007, multiple calculations can be simultaneously displayed.
Tip 8:
Add to the list:
[CTRL]Home - Move to upper left most navigable cell (usually A1).
[CTRL]End - Move to the intersection of the right-most non-empty column and bottom-most non-empty row - great for finding the "endpoint" of your data without having to search.
[CTRL]PageUp / [CTRL]PageDown can be used to navigate from one worksheet to another.
[CTRL]A as noted above for Tip 1.
The list goes on, but these are some I find useful.
Tip 9:
This technique works well for static values, not so well for formulas with some rare exceptions.
Not a criticism, just a few notes.
Tip 1:
"There are, of course, other ways to select all the cells in a worksheet. If you're a keyboard person, press [CTRL]A. If you're a menu person, go to Edit | Select All."
In 2003 there is no Select All in the Edit menu, but [CTRL]A works. 2007 does, however, have the Select All menu item. [CTRL]A is also a great way to select a contiguos range of non-empty cells (such as a table). Just select a cell in the range and follow that with [CTRL]A.
Tip 6:
It can become a mess if not done carefully, but subtotals can be nested or layered if care is taken in selecting the cells to apply the subtotal to. After applying subtotals, if one's desire is to redefine the current subtotals, choose Data | Subtotals and redefine leaving Replace Current Subtotals checked. If your desire is to add additional subtotals, uncheck Replace Current Subtotals and proceed.
Tip 7:
Autocalc will display any one of the available calculations for 2003. For 2007, multiple calculations can be simultaneously displayed.
Tip 8:
Add to the list:
[CTRL]Home - Move to upper left most navigable cell (usually A1).
[CTRL]End - Move to the intersection of the right-most non-empty column and bottom-most non-empty row - great for finding the "endpoint" of your data without having to search.
[CTRL]PageUp / [CTRL]PageDown can be used to navigate from one worksheet to another.
[CTRL]A as noted above for Tip 1.
The list goes on, but these are some I find useful.
Tip 9:
This technique works well for static values, not so well for formulas with some rare exceptions.
Not a criticism, just a few notes.
I received an Excel file that is 650 lines. The lines are listed with part numbers. The file will only sort half. the first half is sorted and the second half is sorted, but I can not the file to sort the whole thing into a continous list. I have tried every way I know and cannot find the answer on the net. Bart Ford bartf@bellsouth.net
Do some of the part "numbers" have a leading blank? Are some formatted as numbers and some as strings?
The numbers are 7 digit part numbers. the file came in all jumbled and I at least in part was able to sort some of it to make the individual numbers to find.
Sounds like you've got 2 different types of data, either use the data/sort function rather than the az button, or you can quickly change the data format by using the text to columns wizard found in the data menu. Hope this helps
Is it possible that some of the parts are numbers and some are numbers stored as text? When I had this problem, I put a numberic one in a cell; select/copy the cell; then do a Paste Special onto the cells that were not sorting properly; and selected the option of Multiply. This multiplied all the cells by 1 making them numeric and then they would sort properly.
.... but nope. The dbl-click on the Format Painter and the use of the advanced filter to get unique entries were new to me - and very useful.
Nice job.
mac
Nice job.
mac
I'm surprised you did not mention the shortcut to copy a sheet by putting the cursor on the sheet tab and while pressing the control key dragging to the right or left.
I'm surprised you did not mention the shortcut to copy a sheet by putting the cursor on the sheet tab and while pressing the control key dragging to the right or left.
1. Select entire sheet (click at top left)
2. Right click. Select format cells.
3. Select protection.
4. Click hIdden and OK.
5. Key Alt TPP. Enter optional password.
6. Copy sheet to another sheet. Only values are copied. Delete columns you do not want to be seen.
7. Save and distribute worksheet.
2. Right click. Select format cells.
3. Select protection.
4. Click hIdden and OK.
5. Key Alt TPP. Enter optional password.
6. Copy sheet to another sheet. Only values are copied. Delete columns you do not want to be seen.
7. Save and distribute worksheet.
Couldn't you select the entire sheet as you described, and press Ctrl+C. Then on the other sheet right click on cell A1 and choose Paste Special>Values. This would give you values only on the other sheet without all the other steps.
#7 - Love it. This had somehow been turned off on my laptop and I couldn't for love nor money find out how to turn it back on. Champion!!!
I always add both the select visible cells and select Current Region to my toolbar. Select visible cells is great for copying if you have hidden some columns and want to paste only what you can see. Select Current Region grabs all contiguous cells for copying.
Number 5 was new to me, I had been using SubTotals to get the unique records.
Number 5 was new to me, I had been using SubTotals to get the unique records.
upgrade this to 100 obscure excel tricks - very useful
Home Ribbon|Clip Board Group|Paste Button|Paste Special|Text (pasting as HTML will usually not allow charting of data)
If data does not parse into individual columns, use Data Ribbon|Data Tools|Text to Columns?
Fixed Width: if Courier font is used or if width of each character is the same
Delimited: a consistent character is used between each piece of data
Step 2 of wizard allows for column adjustment (add, remove or change) for fixed width fonts or adding of a delimiter if it is not listed for delimited text
If data does not parse into individual columns, use Data Ribbon|Data Tools|Text to Columns?
Fixed Width: if Courier font is used or if width of each character is the same
Delimited: a consistent character is used between each piece of data
Step 2 of wizard allows for column adjustment (add, remove or change) for fixed width fonts or adding of a delimiter if it is not listed for delimited text
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
