Software

10 things I can never remember how to do in Excel

If your Excel skills are perennially rusty, this cheat sheet will help you knock out basic tasks without forcing you to scramble back up the learning curve.

If your Excel skills are perennially rusty, this cheat sheet will help you knock out basic tasks without forcing you to scramble back up the learning curve.


Like a lot of business software users, I'm a generalist. On any given day, my job may require a little number crunching and word processing, and maybe some page layout, slide show creation, or report building. For the most part, the various Office apps make it easy to meet these needs. But certain tasks just don't come up often enough for me to keep them in my head. (Yes, I'm blaming the tasks here.)

Excel is a good example. I use it every day, but I seldom need to do more than enter data or a simple formula. So when a job requires something a little more sophisticated, I waste a certain amount of time trying to remember how I got it to work before. Then I waste even more time trying to extract a useful answer from online help.

Frustrating, unproductive, and a little embarrassing.

So I made a list of a few Excel techniques I occasionally need but inevitably forget. This is bunny stuff, and not for you power users out there. But if you're a casual Excel user (or you're on the help desk but don't spend much time in Excel yourself), maybe this list will help you cut to the chase.

Note: This cheat sheet is also available as a PDF download.

1: Toggle the display of formulas

When you need to see what's going on under the hood of a worksheet, you may want to turn on Excel's formula display. There's a convoluted way to do this via Excel options (and Excel 2007 offers the Show Formulas button in the Formula Auditing group of the Formulas tab - if you want to remember that). But you can toggle the display on the fly just by pressing [Ctrl] ~. If you select a cell whose formula you want to troubleshoot before turning on the display, Excel will also show you the dependent cells for the formula.

2: Convert a formula to its results

Sometimes, you may need to replace a formula with its results - either to preserve a static value or to optimize your sheet by reducing calculations. There's a pretty simple trick for this, but a word of warning: Be sure you really want to wipe out a formula before you do it. (There could be undesirable consequences.) In fact, a good practice is to create a backup copy of the workbook as a safety net in case things go awry.

To convert a formula, click in its cell and press [F2] to enable in-cell editing. Next, press [F9] to calculate the formula and display its results. Then, hit [Enter], and your formula will be replaced by the value it produced.

You can also copy the formula and use Paste Special | Values to paste the results someplace else, leaving the formula intact in its original location.

3: Create a copy of an existing worksheet

Excel offers an efficient way to copy a worksheet, either within the current book or into a different one - handy when you need to start a new sheet that includes some or all of the data and/or formatting of an existing sheet. It works like this:

  1. Right-click on the sheet tab of the sheet you want to copy.
  2. Choose Move Or Copy.
  3. Select the Create A Copy check box in the bottom-left corner of the Move Or Copy dialog box.
  4. Choose a different workbook, if desired, from the To Book drop-down list. (That other workbook must be open to show up in the list.) You can also select New Workbook.
  5. In the Before Sheet list box, specify where you want the copied sheet to go within the specified workbook.
  6. Click OK.

4: Start a new line within a cell

This may seem beyond simplistic - until the day you can't remember how to do it. If you need to create a multiple-line entry in a cell, you can't just press [Enter] to insert a line break, since that will propel you into the next cell. Instead, you have to press [Alt][Enter].

5: Unhide hidden rows or columns

From time to time, someone will send me a worksheet with hidden rows or columns. I usually don't need to see the data, so of course I forget how to unhide it on the rare occasions when I do need to see it. It's easy, though: Highlight the row above and the row below the hidden row(s) - or the column to the left and to the right of the hidden column(s). Then, you can reveal the data in various ways:

  • Press [Shift][Ctrl]0 (that's a zero).
  • Right-click the selection and choose Unhide.
  • Choose Column (or Row) from the Format menu and then select Unhide. In Excel 2007, go to the Cells group on the Home tab, click Format, choose Hide & Unhide, and select Unhide Rows or Unhide Columns.

6: Enter a fraction in a cell

Say you type 1/4 in a cell, wanting to enter the fraction one-fourth. Ordinarily, Excel will turn the value into a date - 4-Jan. To prevent that, just preface your entry with a zero and a space: 0 1/4. Excel will leave your fraction alone. Without the zero, you'll see 1/4/2009 (or whatever year you happen to be in) in the Formula bar. With the zero, you'll see 0.25.

7: Simultaneously copy data into noncontiguous cells

To copy data from one cell into adjacent cells, you just drag the cell's fill handle across the cells where you want the copied data to appear. But sometimes, you'll need to copy data into cells that are scattered around the worksheet. The most efficient way to handle that task is to copy the desired data, hold down [Ctrl], and select all the other cells where you want to paste the data. Then, press [Ctrl]V and Excel will insert the copied data into each of the selected cells.

8: Simultaneously enter data into noncontiguous cells

Similar to the previous trick, you can save time when you need to enter the same data into cells that aren't next to each other. Start by holding down the Ctrl key and selecting all the cells into which you want to enter data. Then, type your data and press [Ctrl][Enter]. Excel will insert the data into all of the cells in the noncontiguous selection.

9: Enter text in the same location in multiple worksheets

This may not come up all that often, but it's a cool trick when you need it. Let's say that you're entering month names as column headers at the top of a sheet - and you want them to appear on your other sheets as well. Click in the cell where you'll be entering January. Then hold down [Ctrl] and click on the sheet tabs of the other sheets where you want the month names to appear. This will group the sheets so that what you do now affects all of them.

Go ahead and type January. Then (another cool trick coming...), drag the cell's fill handle to the right across the next 11 cells. Excel recognizes that January is the first item in a built-in series, so it will insert the rest of the month names for you.

To complete the process, right-click on one of the selected sheet tabs and choose Ungroup Sheets from the shortcut menu. If you check those sheets, you'll see your month names have been entered in all of them.

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

Once in a while, I'll set up a worksheet using one structure that seems to make sense, only to realize it would make a whole lot more sense if the rows were columns and the columns were rows. And apparently I'm not alone in this befuddlement, because Excel provides a Transpose option to facilitate the necessary flip-flopping of data:

  1. Select the range of cells you want to transpose and click Copy or press [Ctrl]C.
  2. Click in a new location (not overlapping your selection).
  3. Go to Edit | Paste Special and select the Transpose check box. In Excel 2007, click Paste in the Clipboard group of the Home tab and select Transpose.
  4. You can then delete your original, wrong-structured data.


Check out 10 Things... the newsletter

Get the key facts on a wide range of technologies, techniques, strategies, and skills with the help of the concise need-to-know lists featured in TechRepublic's 10 Things newsletter, delivered every Friday. Automatically sign up today.

About

Jody Gilbert has been writing and editing technical articles for the past 25 years. She was part of the team that launched TechRepublic and is now senior editor for Tech Pro Research.

58 comments
michael_boardman
michael_boardman

Re 7 & 8: Just a caveat... If you do EXACTLY as described (and nothing wrong with that if it's what you need to do), then you will be using whichever cell was selected beforehand, as well as the one you are now clicking. You might therefore inadvertently be selecting that previous cell when you don't want to do so. Hope I made that clear - please post back if it's like mud! EDIT: I mean that if you click while holding down Ctrl, you will select as I describe. (See, I was right to think it might be clear as mud!)

VampireRat
VampireRat

Wow! I didn't realize how many of these I had forgotten. Thanks!!

Xfaysal
Xfaysal

Learned some new stuff today - thank you

plumbsue60
plumbsue60

My problem with excel is not being able to change the setup that is on there. I want a column of one specific day such as Monday but it will not let me do it with the fill handle. When I try, it goes through the week by making the next cell Tuesday, Wednesday, and so on when I want that column to be all Mondays. Can anyone tell me how to break that cycle?

lecaf
lecaf

sorry wrong post

WillfromSF
WillfromSF

"6: Enter a fraction in a cell" does not work if it's in a cell that is previously formatted to be a date and if it's not that way than it's easier and more intuitive to just precede the fraction by a + or = sign.

shogv48
shogv48

Very good Information..Especially 10th one,

djkoval
djkoval

Excelentes Recomendaciones breves pero muy puntuales

Richard Noel
Richard Noel

I cannot get CTRL+SHIFT+0 to do anything. CTRL+0 will hide columns for me, and CTRL+- will unhide rows or columns. HOWEVER, when selecting rows CTRL+0 makes my entire spreadsheet vanish. So far only CTRL+z has brought it back. What am I doing wrong?

nboz
nboz

Does anyone know of a trick to unhide Column A or Row 1? Thanks!

argeedblu
argeedblu

Excel displays the predecessor cells not the dependant cells for the formula. The predecessor cells are the cells the formula depends on (or refers to). Successor cells are the cells that refer to the formula. Glenn

thomsonk
thomsonk

Not knowing how to do that has been an annoyance to me for... longer than I'm willing to freely admit!!! But huzzah!! It's not shift-enter. It's ALT-Enter. Oh frabjous DAY!!

michael.runcie
michael.runcie

One othe THE most useful feature of Excel and OpenOffice is the ability to use array formulas to sum a range on multiple criteria. There is the SUMIF and COUNTIF formulas but only allow one condition. To add multiple conditions, write the formula like this =SUM(IF(A2:A7="Apple",IF(B2:B7="Fresh",C2:C7,0),0)) then, hit CTRL + SHIFT + ENTER. This will put {} brackets around the formula. To do a count the formula should be =SUM(IF(A2:A7="Apple",IF(B2:B7="Fresh",1,0),0)) In my opinion this should be in the top 10.

PaulDriver
PaulDriver

Life is simpler and cheaper. Have it on a USB drive, and I take my environment with me, much easier then dealing with the 4 different versions of Office my clients use, and I don't have to deal with the Ribbon.

stapleb
stapleb

Anoher way to copy a worksheet is hold down the Ctrl key, and drag the sheet tab to a new location in the current book. Release the mouse first as Ctrl is the "copy" instruction. If it is to be copied into another book, have that book open, then use the View options to tile the workbooks side by side. You can now use Ctrl and drag to take the sheet to the other book.

stapleb
stapleb

There is another way you can "copy" data from a cell into other cells. Select the cells to receive the data, using the Ctrl key to select the cells if they are non-contiguous. The last cell to be selected is the one containing the data to be copied. Ctrl + Enter only works if the data to be duplicated is either being typed or edited. So, press F2 to "edit" the cell then Ctrl Enter to copy it into all selected cells.

twistedg
twistedg

Great reminders. I always forget how to enter a fraction as a fraction. Thanks!

Benny7440
Benny7440

There're apparently simple things that could be done within a worksheet that Excel isn't particularly amiable with (the user); you'll see if you try the following: "Make a graph of a cardiod on a worksheet". Escel brings a lot of types of graphs but lacks some useful & basic features that would permit the user to do something like this as "simple" as a common linear XY-graph, one of those is Polar Coordinates. If you try it you'll notice that you have to perform some "programmable thinking even before starting anything: ? fill a column with a series - degrees; ? " the next one with the result of a formula based on the values of the cells of the previous column (relative addressing) - radians; ? " the next in like manner with another formula - trigonometric formula; ? ibid - if you want to incorporate another graph - another trogonometric formula maybe; ? now deal with the configuration of the graph itself. As you see, especially if you tried the experiment & succeeded, isn't a user friendly process. You end up with the feeling that Microsoft can & should do something with this lack of features that would make the tasks like this easier for the common user. Hope this post is read/forwarded to the appropriate people & that it fire up the desire to 'excel' while upgrading something...

guy
guy

I discovered number 9 by mistake. I had grouped by sheets for another reason and was then later entering data into one of the sheets. I thought I was going mad when I selected the next sheet and the data had appeared there as well - overwriting some formulae that I had entered. I didn't take me long to realise what had happened and once I saw the sheet group I saw my mistake. It's proved very useful since then, but be warned that it will overwrite the data in the cells on the other sheets without warning.

jpena
jpena

Technically, it's Ctrl+`, not Ctrl+~. Ctrl+~ is equivalent to pressing Ctrl+Shift+`, which is not the same as Ctrl+` (and doesn't do anything anyway).

jbenton
jbenton

If dragging the fill handle wants to continue a series (as shown by the helpful screentip), holding down ctrl before releasing the mouse will just copy the initial value You could also use a technique described in the article by selecting the column, typing "Monday" and use ctrl-enter to commit (you need a whole column of entries - really?)

Laurie_G
Laurie_G

While you drag down from the cell with Monday, hold the right mouse button. Select copy cells from the shortcut menu.

stapleb
stapleb

Ctrl+- is usually used to delete rows or columns, and Ctrl+ to insert. To get Ctrl+Shift+0 to work highlight a cell either side of the column you wish to unhide. If you have selected a column and press Ctrl+9, which is Hide Rows, you will hide all rows in the sheet - I'm guessing this is what you have been accidentally doing - welcome to my world.

david.hanshumaker
david.hanshumaker

If either has been hidden by intention or accidently by setting the width or height to 0, use the F5 key. In response to the Go To box, enter any cell in col A or row 1, whichever you need to unhide. Press enter. You won't see it, but you will now be in that cell. Once there, format the row height or col width to some value greater than 0.

sliepner
sliepner

If you have tried selecting the whole sheet then Edit & Unhide and it does not do so, then chances are the row/column are not hidden but have been reduced in size so as to appear hidden. I have been caught out with this several times. To check place & hold cursor at edge of unseen column/row, if the usually bold cross with two arrows is not bold & has a split in the middle, the row/column has been reduced to 0, click, hold and resize to suit.

QueryEverything
QueryEverything

Sure. In the top left, above and to the left of the cell A1 is a control to select all cells. Click in it. To unhide row 1 right click a viewable selected row id and select Unhide. To unhide column A right click a viewable selected column id and select Unhide.

cartographer
cartographer

The array is a great tip, and underrated - but Excel 07 users also have SUMIFS and COUNTIFS which now allow many conditions to be applied to the range.

Laurie_G
Laurie_G

I looked at moving to Open Office after using it for a few months - indeed impressive. But then I wanted to use my time-saving macros that I'd used and grown over the years and found I would have to migrate them to python - the Open Office programming language. Then I strated to look at the language, and though not a million miles from VBA, would require a considerable learning process. I also thought about finding a migration tool, but that would limit me to what I have in stock or to coding in one language and translating to another. Doubtless at some some point in the future, both languages will converge. However, the changes in 2007 and it having to work in compatibility mode with all of my stock of files does make me think again.

rgpavc
rgpavc

Being a user of Word and Excel since version 1, I wonder why you teach people for 15 years how to use your tools and then change it with the ribbon? Points brought out in this posting will be even more remote after a few more years of the ribbon (in)convenience. We ought to be able to remove the ribbon and customize some useful software to help us spend less time trying to find out how to do crap and spend more time in getting done what needs to be done! After all, getting stuff done helps the pay check, knowing where programmers what on which ribbon only wastes time. RPayne

rgmwilliams
rgmwilliams

Tip #9 is great, but people inadvertently have sheets grouped together. I don't know people who find this useful, but I do know many people who've tripped up on this!

Laurie_G
Laurie_G

Having had a discussion with some Swedish people recently, we established that shortcuts vary with Keyboard layout - theirs having the : and ; on different keys and giving them different combos i.e. : + shift that US & UK keyboards don't. Worth bearing in mind if you have to support someone outside of your country :) A nightmare when I moved to Holland from the UK, as some shortcuts change due to language not just layout.

pjcronje
pjcronje

Ctrl + Shift + ` DOES do something - it reverts the current cell number formatting to "General".

p@re
p@re

I use Ctrl-Shift-2 to show/hide formulas

sestarr97
sestarr97

I'm waiting for the day when Excel and the other office apps give me a quick option for Paste Special (right clicking or an icon) instead of having to Alt-E-S or use the click three times on the mouse. I use it all the time to cut and paste whether its within a spreadsheet or text from the web .

stapleb
stapleb

You can highlight the range, including the cell containing the data to be copied, then press Ctrl + D which is Fill Down. If you wish to fill across then Ctrl + R for Fill Right.

p@re
p@re

Great ! So you don't need to change the pre-defined lists... Happy holidays guys.

nboz
nboz

Even though I would right-click the header for Column A and choose Hide, the only way I ever found to get it back was to drag the two-headed arrow at the beginning of Column B's header. Glad I wasn't the only one.

nboz
nboz

Look at all the options I missed! Thanks very much.

nboz
nboz

So simple in retrospect, but I thought I had tried everything. I appreciate your response.

thomsonk
thomsonk

Just happened to catch this as I was updating my profile. Fantastic! I can picture your little Jabberwock.

p@re
p@re

Great to hear that. I use SUMPRODUCT for multiple conditions but it is slow in calculation ... or I concatenate different fields and use a simple SUMIF. But it's tedious... I'll have now a couple more tools to use. Thanks guys !

Rideastar
Rideastar

IT brought me that worthless ribbon and after spending 2 weeks fumbling for commands that I knew and used constantly - I finally demanded that they give me what I had. I still get pressured from time to time but I have held off for 2 years so far. OK - so maybe the Ribbon will make a completely new user more productive. That does not justify cramming it down the throats of the more proficient users.

DBlayney
DBlayney

If you have a Worksheet_Change function defined for the grouped sheets, it does not always appear to work on every changed sheet. This was certainly true in earlier Excel versions, I have not throrougly checked it in Excel 2007 yet

stapleb
stapleb

I think this is an extremely useful feature. If you want sheets in a book to have the same layout, you can group the sheets, and then the format, layout, etc is applied to all selected sheets. Also you can have the same headers and footers for the selected sheets. The Title Bar will always show Bookname(Grouped) so there is a hint at the top of the screen. Also the colour (sorry, I'm an Ozzie so can't help the spelling) of the tabs indicates they are grouped, so you have a couple of visual indicators.

twistedg
twistedg

In versions through 2003 (haven't looked in 2007 yet) Paste Special is an available button you can easily add to the toolbar. I sit mine right next to Paste. Only problem is it doesn't have a picture. I've always copied the image from Paste and edited the little document to look red.

bsnake
bsnake

Agreed. They have helped with the ribbon bar in Office 2007 there is a down arrow under the big Paste button. I use it all of the time. I use Paste Special all of the time for pasting unformatted text so my text will assume the formatting of the text where I'm pasting it. I remember in older versions of office changing the paste option to paste unformatted text by default.

njconner1
njconner1

The fastest way I've found to ungroup sheets once I've finished doing the grouping techniques discussed here: If you have ALL tabs grouped, clicking on any individual tab will ungroup your tabs and just select the single tab you clicked on If you have SOME tabs grouped, clicking on any one individual tab that is outside of the group will ungroup your tabs and select whichever individual tab you selected.

stapleb
stapleb

If you like keyboard shortcuts, Insert row or column can be done by using Ctrl and Plus. Delete row or column can be done by using Ctrl and Minus. Haven't discovered the Paste Special shortcut yet.

awgiedawgie
awgiedawgie

I also have added Paste Special to my toolbar, but I don't bother with the picture. I edited my toolbars so three of my most-often-used functions can be launched with simple Alt+key combinations. Alt-S for "insert rowS", Alt-D for "Delete rows", and Alt-A for "pAste special".

DBlayney
DBlayney

Paste Values is available to add to the QA Toolbar. Click the "Office" button; select Excel Options; Customize; All Commands - everything is there. Like an earlier contributor, I have built up my QA Toolbar to match (more or less) what I had in Excel XP (2002). I miss the ability to edit icons too, and the chance to create more special purpose toolbars but I can now work quite happily with the ribbon hidden (Ctrl-F1)

Laurie_G
Laurie_G

I have a similar thing for pasting HTML code - useful when researching stuff that you need to record in a sheet. It pastes clipboard data as text, otherwise HTML overwrites adjacent cells and puts in all sorts of nasty formats. I'm just getting to grips with 2007 and so far have a quick access toolbar that is beginning to resemble my older version personal one. I can't customise the buttons (yet!), but I just add buttons and link them to code in my personal workbook.

p@re
p@re

That's funny... I'm doing exactly the same with the icon colored in red... And I also added Paste Formats that I colored in blue. This was in 2003 and before. In 2007, we can't edit the icons as far as I know... too bad...

njconner1
njconner1

I actually made a few paste special macros and added them to my toolbar in 2003, before realizing the same thing! Having paste special on your toolbar is a huge time saver if you work with excel for any extended amount of time. In 2007 they include most of the paste special options on the standard ribbon.

bsnake
bsnake

Also, right-clicking in the destination offers "Paste Special..." right under Paste, so it seems that they have already addressed your concern.