Your Excel Hints and Tips

[Martynt74]

I'm no stranger to a long confusing looking formula in Excel and sometimes it can take me ages to work out what i've actually done.

I was recently sent a file from someone who'd used "Alt-Enter" in the formula bar to lay out the formula to be more "code like"

I thought it was handy and made a note to try and use it in practise.

It's turned this confusing shambles of text from this

to this, which i'm sure you can agree is much easier on the eye!

So what are your tips when working in Excel?

 

[Confused Stu]

Good suggestion about splitting long formulae into lines, never thought of that!

It's a simple one, but it's amazing how many people don't know it:
- use CTRL and a direction to take you to the end of a block of entries (if you're in a cell with an entry) or to the first occupied cell (if you're in a blank one)

Can save loads of time when I see people scrolling to the bottom of a 10,000 line table. Press CTRL+Down Arrow and you're instantly at the bottom. Just make sure you're in a column/row that has continuous data, if you've got a list of names and figures against most/some of them, make sure your cursor is in the name column before pressing.

The other one that people seem to think you're some kind of magician is when you use VLOOKUP or HLOOKUP. They're simple formulae, but you can waste so much time if you don't know about them.

 

[Maccapacca]

I've got a real problem with excel. Everything I receive to manipulate is in a different format, literally everything every day even from the same source over and over. I've become a master at bodging excel really simplisticly

 

[FloppyPoppy]

if you struggle with excel don't rush be patient and don't be embarrassed about asking anyone for help.

If your good at excel and someone asks you to show them how to do something. Don't be a **** about it or your just going to make them feel like ****.

 

[Quartz]

to this, which i'm sure you can agree is much easier on the eye!

You are absolutely right. Clarity helps a lot in programming. That said, it still looks horrible. Can you not set up a name range with CARBON, RESIN, etc, then test for membership and use one of INDEX, MATCH, HLOOKUP, or VLOOKUP as appropriate to return the result? At first glance, you might need to use the INDIRECT function to use the name returned as the sheet name.

And make those ranges clearer with names.

Actually, you might consider unwinding the IF statement, calculating all eight results, then choosing the appropriate one? It might actually be faster. My brother runs spreadsheets many, many gigabytes in size and has found this to be faster because of Excel's excellent multi-threading capabilities (except for VBA, which is single-threaded). Excel will recalculate separate sheets on separate cores.

 

[james.miller]

which i'm sure you can agree is much easier on the eye!

It is, until you start using named ranges and it becomes more of a hindrance than a help. My tip would be to keep it simple and split those uber complicated formulas across multiple cells and do this:

You are absolutely right. Clarity helps a lot in programming. That said, it still looks horrible. Can you not set up a name range with CARBON, RESIN, etc, then test for membership and use one of INDEX, MATCH, HLOOKUP, or VLOOKUP as appropriate to return the result? At first glance, you might need to use the INDIRECT function to use the name returned as the sheet name.

And make those ranges clearer with names.

^^^ that.

I'll think of more later but for now some generic ones;

- F4 when entering cell addresses.
- name ranges and reference them wherever possible
- use tables (and name them / reference them) wherever possible.
- Yes INDEX/MATCH is faster than lookups in most cases but unless you need that speed just use lookups. Soo much easier to work with

 

[wesimmo]

It is, until you start using named ranges and it becomes more of a hindrance than a help. My tip would be to keep it simple and split those uber complicated formulas across multiple cells and do this:



^^^ that.

I'll think of more later but for now some generic ones;

- F4 when entering cell addresses.
- name ranges and reference them wherever possible
- use tables (and name them / reference them) wherever possible.
- Yes INDEX/MATCH is faster than lookups in most cases but unless you need that speed just use lookups. Soo much easier to work with

Splitting up complex formulas into columns that show your working is one of the best tips.

It's something I encourage with my team at work, if you're any good at Excel you'll have, at some point, done something really clever that made sense at the time. However a few months later you don't even know what you did yourself, never mind anybody else picking it up and working it out.

 

[Quartz]

if you're any good at Excel you'll have, at some point, done something really clever that made sense at the time. However a few months later you don't even know what you did yourself, never mind anybody else picking it up and working it out.

Guilty as charged!

I haven't used Excel in anger in years so I've lost most of my skills, but every now and then a thread like this fires a synapse.

 

[GWillow]

Simple little one : Ctrl+; (semi-colon) put's the current date into the current cell.