MS Office

Exact difference between two dates

2010-10-10T06:45:27+01:00

Dates are just numbers in Excel.  To calculate the number of days between two dates, just subtract one from the other (you might find Excel automatically formats the answer as a date, in which case you need to change the number format back to 'General' or 'Number'). However, it's also often the case that you need to know the number of whole years or months between two dates.  A recent example - has this person turned 17?  In this case, you can either write a complicated formula to calculate it, or you can use the Excel function DATEDIF() - read it [...]

Exact difference between two dates2010-10-10T06:45:27+01:00

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

Styles are the key to using Word efficiently

2010-09-21T18:15:15+01:00

If you don't know about Styles in Word, you should.  When I discovered Styles, they fundamentally changed how I use Word.  To give you an idea, with Styles you can Apply your formatting (any formatting!) with a single click Ensure consistency of formatting Automatically generate tables of contents Move large chunks of document with a single click Make your document text flow more efficiently ...and much, much more. Microsoft obviously believe that styles need highlighting, because they have moved from being an obscure menu item to the middle of the Home tab.  If you've never done anything with styles, you need [...]

Styles are the key to using Word efficiently2010-09-21T18:15:15+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

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

Title

© Out of the Hat Ltd   |   All Rights Reserved   |       web@ooth.co.uk 
Go to Top