Excel Tips Archives - Page 2 of 2

Self-expanding data ranges 2 – use Tables

2010-09-28T19:14:48+01:00

In Excel 2007, Microsoft have given much more prominence to Tables. They are useful for formatting data, filtering and producing summary statistics, but they have another use - if you refer to a table (rather than a range) in a formula, they automatically expand when you add data! First, create your table (select a cell in the data, then 'Insert' tab, 'Tables' group, 'Table' command). Now, when writing a formula and wanting to include the table, just highlight it as you would when entering a range - rather than putting in the cell range, Excel will enter something like [...]

Self-expanding data ranges 2 – use Tables2010-09-28T19:14:48+01:00

Structuring your spreadsheets

2010-09-28T18:43:34+01:00

When you first start using Excel, it seems like a good idea to break your data up as much as possible so that it's easy to find/read. It's not uncommon to have lots of sheets containing similar data for different departments / people / products / stores / months etc. However, as you start to learn the power of Excel, using functions such as pivot tables and filters, you'll find that it begins to make sense to keep source data in one big table that's easy to analyse - it's much easier to summarise a big block of data than it [...]

Structuring your spreadsheets2010-09-28T18:43:34+01:00

Importing legacy data

2010-09-23T18:45:09+01:00

Many legacy systems export their data in human-readable form, rather than in blocks of data that are efficient for analysis. This graphic is an example, where the product code is listed once, then the variants are listed next to it. For analysis, we need to generate a column that has the product code in every row that has other data. The formula in the middle box of the graphic does this. It roughly translates as "if the cell to the right is not blank, use it" (this picks up the product code where it appears) "otherwise, if the corresponding [...]

Importing legacy data2010-09-23T18:45:09+01:00

Selecting large ranges – use the keyboard!

2010-09-18T06:22:27+01:00

There are a few things that many people struggle with. One of them is using the mouse to select a large range of data that goes off the visible screen. By combining two simple keyboard shortcuts, you can select a large range of data with two keypresses.

Selecting large ranges – use the keyboard!2010-09-18T06:22:27+01:00

Don’t type cell references – let Excel do it for you
Gallery

Don’t type cell references – let Excel do it for you

2010-09-18T04:23:59+01:00

I try to avoid typing cell references wherever possible, because I know I'll get them wrong. Much better to let Excel type your cell references for you. It's quite straightforward.

Don’t type cell references – let Excel do it for you2010-09-18T04:23:59+01:00

Use columns in formulae to automatically include new data

2010-09-18T04:14:28+01:00

Usually, when you write a formula, you refer to a range of cells, e.g. =SUM(A1:A5). However, if you are continually adding new rows to the data, you don't want to be constantly updating the formula. Well, why not make the formula refer to the column, rather than a range - then it'll include anything in the column. Example: =SUM(A:A) will add up everything in column A. It works for rows, too, e.g. =SUM(1:1). Note that, when defining a column or row in Excel, you need to define the start and finish column/row, separated by commas - even if they are the [...]

Use columns in formulae to automatically include new data2010-09-18T04:14:28+01:00

Self-expanding data ranges

2010-09-17T08:07:00+01:00

Isn't it irritating when you keep having to go back into wizards, dialog boxes and formulae to manually change data ranges? You can often get round this problem by using column references or tables. There are times when you want a range to expand itself based on the size of the data, and here's a way to do it. Because of the way this works, it's best to have your data on its own sheet, without anything else with it. You also need to make sure there's always data in the first row and first column. Put some data in and [...]

Self-expanding data ranges2010-09-17T08:07:00+01:00