What are the best Excel tips? [closed]

Question

What are your best tips/not so known features of excel?

Answer 1

My best advice for Excel is to know its limitations. Engineer-types love Excel, but it's over-used to be:

• A database
• A report generator
• An application deployment mechanism

If you need any of these things, think before blindly firing up Excel.

Answer 2

In-cell graphs, using REPT. The simple form (in column C below):

=REPT( "|", B2 / 5 )

and negative/positive (in columns G and H below):

=IF( F2 < 0, REPT( "|", ABS( F2 / 5 )), "" )
=IF( F2 > 0, REPT( "|", ABS( F2 / 5 )), "" )

Use Arial Narrow or similar. Of course negative values are right-justified and red.

Answer 3

The fill handle:

• It works based on your current selection, so if you select a series (1, 2, 3), it can continue this for you. I'm sure many people know this.
• Click & drag for ordinary extend-series or fill. The basics.
• Drag inside to clear cells.
• Double-click to fill all the way down to the bottom of the column, or to match the end of the column to the left if the rest of the cells are blank.
• Control-drag to repeat the selection. If you have a series (1, 2, 3) it will repeat it (1, 2, 3, 1, 2, 3) not extend it (1, 2, 3, 4, 5, 6).
• Shift-drag to insert cells. It will push cells over accordingly.
• Shift-drag inside the selection to delete. It will again shift cells appropriately.

Answer 4

I really like the Text to Columns feature.

Answer 5

The least known and perhaps the most powerful feature of Excel has not even been mentioned yet. The true means of harnessing your inner office guru lies in array formulas (better known as CtrlShiftEnter formulas.

Some other great ones are using OFFSET() to create dynamic ranges, or INDIRECT() to create dynamic cell references. Actually if you spend more than an hour a day in Excel then it would behoove you to learn array formulas and everything listed as a lookup/reference function in excels help section.

Answer 6

When you're pasting, use the right-click option of Paste Special->Values only so that your formatting doesn't change everytime you cut-and-paste.

Answer 7

End, <Arrow Key> moves to the last cell in a block in the given direction.

Answer 8

I really like the SUMIF function.

Answer 9

I find VLOOKUP useful for comparing lists or quickly combining different information

Answer 10

Beware of "Scroll lock" button.

Last time it took me a couple of hours to find out why what excell working the way it was.

Answer 11

I have to say the paste special / transpose option.

I had a huge Excel file that took me about an hour to write and only when I finished, I thought it would be best to switch it around so that fields are vertical instead of horizontal.

There may be a quicker way to do it, but I did a Ctrl+A then open a new worksheet and did Paste > Paste Special. This quickly (Instantly) swapped the Axis around so everything that was vertical was horizontal and visa versa.

It is a brilliant feature!

Answer 12

The If Function

Answer 13

Hiding columns. May seem like a dumb thing but it means that you can create complex sheets without having to having massive impossible to debug formula. Put your logic and calculations together bit but bit and then hide your working.

When you come to debug or change them you'll be glad you did.

Answer 14

My favorite is combining the INDIRECT() function with the ADDRESS() function to retrieve data from another worksheet.

Answer 15

I was on a Excel project for about 8 months and we had an Excel sheet doing crazy things like acting as a 24-hour Windows service monitoring other Excel sheets and creating reports from them, here's the basic code that does that:

http://tanguay.info/web2008/codeExample.php?id=903

and all of my excel tips from that era, there's more to Excel VBA than you think:

http://tanguay.info/web2008/searchResults.php?searchText=excel

Answer 16

Here is one that is a big time-saver for Excel 2007. If you can open excel relatively quickly, but double clicking on a spreadsheet in the file system introduces an extremely long pause between the time excel opens and the spreadsheet comes up, you can fix it with the following procedure:

1. In explorer go to the tools..Folder options menu.
2. Change to the file types tab
3. Select a file type that is registered to excel (for example XLSX)
4. Press the advanced button
5. Select the "Open" item from the actions list and click "Edit..."
6. UNCHECK "Use DDE"
7. Make sure the "Application to use.." box ends with /e "%1" (with the quotes)
8. Repeat steps 3-7 for any file type registered to EXCEL that you frequently use.

On my system, this reduced the time to open a spreadsheet when I double clicked the file from about 45 seconds down to about 4 seconds.

Answer 17

I like the Auto Fill feature using the Fill Handle.

It even works with names of days or names of months:

• type "January" in A1
• type "February" in A2
• select the 2 cells
• then pull the 'fill handle' (little black square on the right hand side lower corner) down.

Answer 18

Using the 'data' menu to sort your data by a certain column. Just highlight your column or block of data and it get sort happy.

Answer 19

1. Database Functions (e.g. DSum, DCountA). If you learn those, you can sum or count on any subset of data in your list.
2. Grouping and Outlining Data. When most people want to see only the labels and the totals (and not the cells containing the detail) they usually hide columns, causing other viewers to constantly unhide and re-hide. Grouping and Outlining lets you hide or unhide the detail with a single click.
3. Subtotals tool under the Data Menu
4. Selecting "Visible Cells Only" to copy and paste subtotal data
5. "Show Pages" in Pivot Tables puts the data from each member of a page area on its own sheet.
6. Custom AutoFill Lists. Others have explained how you can use autofill to display things automatically like the months of the year or days of the week. You can create your own list so that autofill will display your entries automatically. In 2003, go to Tools --> Options --> Custom Lists. In 2007, go to the Office Button --> Excel Options --> Popular --> Edit Custom Lists. You can also create a custom sort order this way.
7. Advanced Filters let you view any subset of data from your list or database.

Answer 20

1. I don't see that anyone has stated the obvious so I will: the presence of VBA in Excel makes it a very powerful platform, as long as you use it with caution. So the tip here is to learn VBA and use it.

2. Protecting worksheets and workbooks comes next. Nasty users, I constantly have to keep them from destroying all my hard work. ;)

3. The Camera tool. Awesome for so many things in my daily work.

4. Use Named Ranges as formulas instead of as a link to a location. Here's a good example of this.

Answer 21

The easiest and fastest way to sum up list of values:

1. Select cell under (or at the end of) column (or row).
2. Press [left Alt] + [=].
3. Press [Enter].

That's it :)

Answer 22

Oh my god. There's a an undocumented DATEDIF function!

Can be used to calculate completed years or months between dates. Why did they hide that gem?!

Answer 23

Way back in excel 97, they had a flight simulator. I don't think it works anymore. That's the best I've got.

Answer 24

Tons of Excel tips and doo-dads are here.

Answer 25

When compiling your program inside a .dll, Excel with its VBA makes a nice GUI interface. Much quicker than making a GUI yourself, and works equally well.

Answer 26

Hold down Shift and drag around a selected block of cells (point to the edge of the selection).

Answer 27

Found a nice use for ARRAYFUNCTION() recently.

Answer 28

One more:

Goto Special: Use Control-G and then Alt-S to select "Special" items, such as all of the Objects, Visible cells only, Row/Column differences and so on. Really useful and mostly hidden in the UI.

Answer 29

Put this formula into an empty header row cell: =SUBTOTAL(3,A2:A3000) When you filter data, the cell will show the number of filtered rows.

Answer 30

1. Linking graphs directly to PowerPoint or other office program, to automatically update reports or presentations without re-pasting. e.g. withing Word

   Insert -> Object select the Excel file, and check link

2. Customs Lists, e.g. great for autocomplete foreign languages months names

   Excel Options -> Advanced -> Edit Custom Lists

3. Paste Special Transpose is a must, and the operations are very fast way to do Add, Subtract, Multiply and Divide without using formulas.

   Copy the value, then in Paste Special select the operation

4. Fastest Autocomplete within ranges

   Double click the little black square (instead of dragging it)