Software optimize

10 obscure Excel tricks that can expedite common chores

Some Excel users waste a lot of time (and add to your support burden) because they don't know the smartest ways to work with spreadsheet data. Here are some highly useful tips that can streamline typical tasks and boost productivity.

Over the years, each new version of Excel has introduced some nifty features. However, buried within Excel are lots of cool features that too few users have discovered. If you (or your users) perform certain tasks every time you use Excel, you'll want to make sure you're taking advantage of these time-saving shortcuts.

Version note

These tips apply to Excel 2003, although most of them work the same way in earlier versions of Excel.

This post originally appeared as an article and is also available as a PDF download.

#1: Select All with one click

The next time you need to select an entire worksheet, click the little gray box in the top-left corner of the sheet. As shown in Figure A, it's the space above the row numbers and to the left of the column letters.

Figure A

Why would you want to select the entire worksheet? Let's count some of the ways:

  • With the entire worksheet selected, you can copy it from one workbook (XLS file) and then paste it into a worksheet in a different workbook. Selecting the whole worksheet ensures you won't accidentally miss something. Note: If you want to make a copy of a worksheet within the same book, just right-click on the worksheet tab, choose Move or Copy, then select the Create A Copy check box.
  • With the entire worksheet selected, you can quickly and easily change the font in all cells or apply formatting to all cells.
  • With the entire worksheet selected, you can double-click on any line separating two column letters or the line separating any two rows. Doing so tells Excel to adjust the width of the columns or the height of the rows to accommodate the data in the cells, which is very helpful if you've just shrunk (or enlarged) the font size of the text in your cells.

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.

#2: Copy the formatting (attributes) of one or more cells and apply them to another cell or range

Once you learn to use the Format Painter tool (which looks like a little yellow paintbrush on the Standard toolbar), you'll wonder how you ever got by without it. To format a cell (or cells), select a cell (or cells) that are formatted the way you like and click Format Painter. Then, click and drag to apply that formatting to another cell (or range of cells).

Here's an example to illustrate how Format Painter works. Start by manually formatting cell A1 as Times New Roman 9, bold, and underlined and then use the Fill Color tool to make the background of the cell yellow. With cell A1 selected, click Format Painter. You'll notice that Excel displays a paintbrush next to the cursor, as shown in Figure B. While that paintbrush is visible, all you have to do is click (and/or drag) to apply all of the attributes from cell A1 to any other cells (Figure C). This shortcut saves time because you don't have to manually reapply the font and other attributes to other cells.

Figure B

Figure C


Note

By default, Format Painter is a "once and out" function. That is, if you click once on Format Painter and then paint a cell or range of cells, Excel will stop painting as soon as you release the mouse. If you want to paint a number of different cells or range of cells, double-click on Format Painter. The paint brush function will then continue painting cells as long as you keep clicking. To turn off Format Painter after you've double-clicked it, press [Esc].


Bonus tip

The cool thing about Format Painter is that it isn't limited to picking up the attributes of one cell at a time. For example, suppose you have applied different formatting attributes to two or more cells in the same row or column, as we did in Row 2 of Figure D. Using Format Painter, you can transfer all those formats to the rows below, as shown in Figure E.

Figure D

Figure E

#3: Perform one-click data mining with AutoFilter

If you support Excel users, AutoFilter will be a surefire hit, both for beginners and for experienced users who haven't seen this feature in action before. Go to Data | AutoFilter, and Excel will add drop-down arrows to the first cell in each column of data in your sheet. When you click on any of those drop-down arrows, Excel will display a list of the unique entries in that column, as shown in Figure F. Just select the desired entry to limit the display of records, as shown in Figure G.

Figure F

Figure G


AutoFilter notes

If you look closely, you'll notice that the color of the drop-down arrow changes from black to blue whenever you make a selection. That change in color is a visual cue to remind you that your list has been filtered by a selection from that column.

As you probably guessed, you aren't limited to filtering the list on just one column. You can click on the drop-down for two or more columns, and Excel will display only those records that match your selections in each column.

There are two ways to turn off AutoFilter. One is to click on each of the columns where you made an AutoFilter selection and choose the (All) option. The other way is to go to Data | Filter and select Show All.


#4: Press [Ctrl]~ to display formulas so you can troubleshoot or debug them

If you wanted to troubleshoot a formula in early versions of Excel, you had to click on the cell containing the formula and look at the formula itself in the Formula Bar. In more recent versions of Excel, you can display your formulas by pressing [Ctrl]~. If you want to view the dependent cells for a particular cell, select it before you press [Ctrl]~.

To demonstrate this feature, open any worksheet that contains at least one formula and press [Ctrl]~. Our sample sheet contains two SUM functions. Figure H shows our sheet after we selected cell B2 and then pressed [Ctrl]~. Notice that when you press this key combination, Excel 2003 also displays the Formula Auditing toolbar.

Figure H

#5: Generate a unique list of entries in a column

When you support or teach Excel users, one of the most common questions you'll hear is, "I've got a list with a thousand entries in a column, and many of those are duplicates. How do I generate a list of the unique entries in that column?"

There are at least two good answers to that question. The first answer is to refer back to #3 above: Go to Data | AutoFilter and then click the drop-down list for the column in question. Doing so lets you see the list of unique entries onscreen. If seeing the list satisfies your need, you're finished.

The second answer is the one to use if you want to have a list of the unique entries you can copy and paste elsewhere. To generate such a list, you'll use Data | Filter | Advanced Filter. To demonstrate how it works, we'll use the data in Column B from the sample sheet we introduced in Figure B.

  1. Click on the column letter to select the entire column that contains your data and then copy it by pressing [Ctrl]C, going to Edit | Copy, or clicking the Copy button on the Standard toolbar. (Select the whole column because you'll need the column header.)
  2. Paste that data into a column away from your source data range or in a new sheet. After you paste the data, it will still be selected. However, if you inadvertently deselect it, just make sure the cell pointer is located anywhere in the data you pasted before you proceed. Note: You don't have to select all the data or sort it first for this tip to work.
  3. Go to Data | Filter | Advanced Filter.
  4. By default, Excel will suggest filtering the list "in-place." There's nothing wrong with that, but I recommend copying the unique records to another location, so you can compare the two lists side by side.
  5. As shown in Figure I, select the Copy To Another Location option, select the Unique Records Only check box, and type B1 in the Copy To field.
  6. Click OK, and Excel will copy the unique entries from the source column into the new location. It will even sort those entries in alphabetical order, as shown in Figure J.

Figure I

Figure J

#6: Let Excel calculate your subtotals for you

The Subtotals feature is yet another life-changing tool for those who haven't seen it before. In the old days, before the Subtotals feature was introduced, here's how you generated subtotals: You'd sort your data, manually insert blank rows between the groups of data you wanted to subtotal, and manually insert the appropriate Sum functions. Many Excel users still take that approach when they want to generate subtotals, which is regrettable, since it provides many opportunities for errors.

Here's a quick walk-through of how to use Excel's Subtotals feature:

  1. Save your worksheet under a work name. To do so, go to File | Save As and add "_work" to the original filename. Trust me, you don't want to practice using the Subtotals function using the only "good" copy of your worksheet. As you'll find when you sally forth into experimenting with this feature, a misstep can make a mess of your data that's hard to clean up.
  2. Sort your data on the column by which you want to subtotal. This is an important step, because the Subtotals feature doesn't care if your data is sorted; it will simply subtotal records in the order they appear.
  3. Click anywhere in your source data and go to Data | Subtotals. When you do, the Subtotal dialog box will appear and Excel will take its best guess as to the column on which you want to subtotal and the function you want to use (Sum) for those subtotals. (You can also generate subtotals using a host of other functions, such as Average, Min, and Max.)
  4. Click OK to generate the subtotals.

Figure K shows what our sample data looked like before we generated the subtotals. Figure L shows the results.

Figure K

Figure L


Note

The Subtotals function doesn't automatically expand the width of the columns to accommodate the subtotaled amounts or the labels. We had to widen our columns before we captured the screen shot of our worksheet to display the contents of columns A and B.


At this point, direct your attention to the top-left corner of the worksheet. You'll see, in an extremely tiny font, the numerals 1, 2, and 3. If you want to see only the grand total line, click 1. Click the number 2, and Excel will "roll up" the detail lines and display only the subtotal and grand total rows, as shown in Figure M. Click 3 to display the default view, which is all of the details rows, the subtotals, and the grand total.

Figure M


Subtotals notes

If you want to view the detail rows for just one of the entries in your list, click the plus sign (+) beside that row. To remove the subtotals and restore your worksheet to its original state, go to Data | Subtotals and click the Subtotal dialog box's Remove All button.


#7: Analyze selections with the AutoCalculate menu

This is a handy tip for anyone who teaches spreadsheet skills classes to adult learners. I use this tip to open my Excel classes, and I have been surprised at how many experienced Excel users have never heard of it.

Here's how it rolls. Create a new workbook and enter some random text and numbers. Press the NumLock key and confirm that you see NUM displayed in Excel's Status bar at the bottom of the Excel window.

Right-click in the vicinity of Ready in the bottom-left corner of Excel's Status Bar to display the incalculably valuable AutoCalculate menu. Gone are the days when you manually key a few numbers off of a sheet to get a quick total. Now you can get it off the screen with a few clicks.

As Figure N shows, the AutoCalculate menu calculates results on the fly and saves you the trouble of setting up formulas to calculate things like the sum, minimum, maximum, or average values in the cells you've selected.

Figure N

If you want to have fun, activate any of the AutoCalculate menu options. Then, click and roll through a range of cells that contain various entries and watch as Excel displays the changes in the current calculation on the Status Bar.

It's a nice little feature whose only drawback is that you can't copy and paste the results of the calculations. It's dynamic and view-only. Still, it's great for sanity checks. For instance, you might be looking to the Count for what should be the number of expected data rows plus the row for the column head. Just click on a column head, and AutoCalculate will tell you how many cells in that column contain values.

#8: Love your [Ctrl] key -- for three reasons

This three-fer will demonstrate why you should love and use your [Ctrl] key:

Reason 1 -- Fast navigation. When you press [Ctrl] and any arrow key (north, east, south, or west), you jump to the last populated cell in that direction. Think of using the [Ctrl]-arrow key shortcut as an alternative to pressing [Page Down] to find the bottom row of a data set or pressing [Tab] to find the last column. Bonus tip: Hold down the [Shift] key while you press any [Ctrl]-arrow key shortcut to select all the cells between where you are and where you jump with the [Ctrl] key. Reason 2 -- You can make noncontiguous selections. That's a fancy way of saying you can select any cells you want, regardless of whether they're contiguous--next to each other in a row or column. Hold down the [Ctrl] key while you click on a cell or click and drag through a range of cells. As long as you hold down the [Ctrl] key, you can click and select to your heart's content. Combine this tip with tip #7, and you can use the AutoCalculate tool to analyze any combination of individual cells or blocks of cells. Figure O shows our screen when we used AutoCalculate to sum the cells we selected while holding down the [Ctrl] key.

Figure O

Reason 3 -- Fast data entry. Suppose you want to put the same string, number, or formula into two or more cells. Using the old-fashioned approach, you'd type the string, number, or formula into the first cell and then copy and paste that entry into the destination cells. But there's a little-known time-saving tip that makes short work of placing the same entry in multiple cells. First, select all the cells you want to populate. Type the entry, but don't press [Enter]. Instead, press [Ctrl][Enter]. When you do, Excel will copy what you typed into all of the selected cells.

#9: Transpose data from a row to a column, or vice-versa

Here's a tip that eliminates the need to rekey data. Suppose you've entered your data with three column headings running across Row 1 and three row headings running down Column A, like the ones shown in Figure P.

Figure P

After working with the data for a while, you decide you'd rather have the current set of row labels (months) running across the columns. Whatever you do, don't even think about rekeying the data.

You'll find the best solution under the Paste Special menu. Start by selecting and copying your entire data range. Click on a new location in your sheet, then go to Edit | Paste Special and select the Transpose check box, as shown in Figure Q. Click OK, and Excel will transpose the column and row labels and data, as shown in Figure R.

Figure Q

Figure R


Note

You aren't limited to using the Paste Special | Transpose option to rearrange multiple rows and columns of data. It works just as well when you need to turn a single row of labels into a column, or vice-versa.


#10: Convert calculations to literal values

This jewel, like #9, is also found under Edit | Paste Special. Experienced users may squawk that this tip doesn't qualify as obscure. However, I decided to include it because, in teaching both beginning and advanced Excel classes to thousands of adult learners over the years, I've met too many people who never even heard of it--which is a crying shame.

Here's how it works. Suppose you have a worksheet with columns and rows chock full of calculations, running the gamut from Sum functions to If tests to vertical and horizontal lookups. The calculations are correct and your data is pristine. You save the worksheet.

Now you need to use a subset of that worksheet in another worksheet. If all you're going to do is print the subset of columns or rows, you can simply hide those rows and columns, print what you need, and unhide the columns and rows later to restore the sheet to its normal state.

But if you're going to e-mail a copy of the spreadsheet to a coworker or a third party, you may not feel comfortable simply hiding certain rows and columns. You may want to delete them instead. The problem is, of course, if you simply start deleting rows and columns, you're going to get error messages in the cells that depend on the cells you deleted.

The solution? First and foremost, save a copy of your pristine spreadsheet under a new name. Just go to File | Save As and add "_work" to the end of the "real" name. Use the Select All tip (#1) to select the entire sheet and then copy it. Without moving the cursor, go to Edit | Paste Special. Now, select the Values option, as shown in

Figure S,and click OK. When you do, Excel will replace all the formulas with the values they're currently calculating and displaying. At that point, you can delete columns or rows and move cells around without generating a single error message.

Figure S


Note

If you use the Paste Special | Values option and the data you're pasting contains calculated dates or numbers formatted as currency, the date calculations will be pasted as the Julian date value, and the currency will lose its dollar signs and commas. To preserve that kind of formatting when you convert calculations to literals, simply choose the Values And Number Formats option (instead of Values).

43 comments
Rashidgg
Rashidgg

Nice for novice to experienced

mynameisobobi
mynameisobobi

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

manirathnam
manirathnam

upgrade this to 100 obscure excel tricks - very useful

tngotu
tngotu

It's very good. it helps me too much. Thanks

Robert.Shaw
Robert.Shaw

Good Stuff! I use excel a lot.. and never knew about double clicking the format painter to turn it "on" until it's turned "off"... It works in Word too. Bob

DuhGreek
DuhGreek

I use ???Paste Special??? to convert to values so often that I put a macro with a keyboard shortcut in ???Personal.xls??? so that it is available every time I start Excel. Whatever is selected will be converted to values, from a single cell to an entire sheet. Application.CutCopyMode = False With Selection .Copy .PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End With Application.CutCopyMode = False

techrepublic
techrepublic

Can't get enough of these random quick tips! Always something useful amongst them, and the ones we know we should be reminded of to make sure it is drilled into our brains!

kilikopele
kilikopele

You can dedupe large tables of data using the Advanced Filter function by clicking "Unique References Only". I use this trick daily. Or am I not obscure enough?

matt.j.steele
matt.j.steele

You can right click the sheet tab and select "Move or copy" to copy the entire sheet to any other open workbook - this is not restricted to just the active workbook. You can also copy to a new workbook.

jazcat
jazcat

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.

boss429
boss429

#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!!!

msd1107
msd1107

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.

cardfan71
cardfan71

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.

cardfan71
cardfan71

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.

torontomac
torontomac

.... 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

bartf
bartf

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

JodyGilbert
JodyGilbert

Are you familiar with all the techniques covered here? (Are your Excel users?) What other timesavers would you add to the list?

david.white
david.white

This trick also works in Access if you want multiple items on a Form using a tool. For example, if you want several command buttons: 1. Double click the Command Button Tool on the Toolbox - to lock it down 2. Click in different places on the form for each button you want to create 3. Click the tool on the Toolbox again, or press the Esc key when you're done to release the tool This even works when you are using the Toolbox wizard. Every time the wizard completes, you can start again until you release the tool. Probably out of place on an Excel forum. But maybe I'll be forgiven. :-)

david.white
david.white

There's a shortcut! Hold Ctrl down, then click and drag the sheet tab. Release the mouse button first when the little arrowhead indicator is where you want your copied sheet to go. This creates a copy of your sheet with the original name appended with a number in brackets.

UrDaddie
UrDaddie

After copying the worksheet, do a CTL-A to select all cells, and then do a Edit/Paste Special/Values to replace everything with the values. This will eliminate all formulas. I think that was his point, right?

john.demontjoie
john.demontjoie

Didn't think you'd show me anything new, but did indeed find something! Many thanks. :o)

JustDave
JustDave

All you have to do is choose the workbook you want from the To Book dropdown. Choose (new book) to create a new workbook on the fly

babycody
babycody

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.

mleeseberg
mleeseberg

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.

robert.terry
robert.terry

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

Level
Level

Do some of the part "numbers" have a leading blank? Are some formatted as numbers and some as strings?

diftone
diftone

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.

babycody
babycody

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.

johnogden
johnogden

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.

kattoon
kattoon

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!

birminghamluskdebora
birminghamluskdebora

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.

Level
Level

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.

suirauqa
suirauqa

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).

bartf
bartf

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.

babycody
babycody

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.

rj.barb
rj.barb

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?

torontomac
torontomac

Ditto - but instead of a macro, I mod the menu with the Paste Values icon: a clipboard with 12 on it. Mac

Writerdm
Writerdm

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.