Tuesday 23 July 2013

Filling data down multiple sheets

By , Director, Naturally Sales Ltd.

There are three ways to copy data and formulae down a spreadsheet.  

For small amounts of data you can use a copy and paste commands.  

Filling down a number of cells within one worksheet the easiest way is to drag the content.  
  • Select the cell you want to copy.  
  • At the bottom right corner of the cell there will be a small black square.  Hover the most over the square until the cursor becomes a black cross.  
  • Click the left mouse button and drag the content as far as you wish.  
If you wish to fill down over multiple sheets you can double click on the little black square. 

In Excel 2003 and Excel 2007, this will automatically fill the data as far as the lowest data in any column to the left. In Excel 2010 this will fill as far as the lowest data anywhere in the spreadsheet. This slight difference in behaviour can be tricky when upgrading to 2010.

We look at more Excel shortcuts in Introduction to Microsoft Excel.

Monday 8 July 2013

Finding text within a cell part 2: the FIND function

By , Director, Naturally Sales Ltd.

Continuing our theme of finding text within a cell, we’ll look now at the FIND function.

The FIND function allows us to see where in a cell’s text a certain string is found. For example, if we look for the letter “t” within the word “Date”, it would return a “3”, because “t” is the third letter of “date”. It’s constructed like this:

=FIND(text,within_cell), or =FIND(“t”,A3) where A3 contains the word “Date”

If Excel is unable to find the text you’re looking for, it will return an error message.

In a future e-tip, we’ll look at how we can combine the FIND and IFERROR functions to see whether or not certain cells contain certain words.

Tuesday 2 July 2013

Finding text within a cell: the IFERROR function

By , Director, Naturally Sales Ltd.


Many people are familiar with using VLOOKUP, INDEX/MATCH or similar lookup formulas to find cell values from a list. However, when we start trying to find a text string within a cell, or to see whether a cell contains certain text or not, things become more difficult.
Unfortunately, Excel doesn't give us a formula to ask it to find cells which contain certain text, along with other text, in the cell body. However, there are techniques we can use. This will be a 3-part e-tip as we address this useful topic. The first part is to learn the IFERROR function.

The IFERROR functions allows us to substitute an error value in a cell for something else of our choosing, and it works by wrapping around the formula you're creating which may (or may not) return an error value. If works as follows:

    =IFERROR(formula,value_if_error)


for example:

    =IFERROR(SUM(A1:A10),0)


...which will return the sum of A1:A10 unless that sum is an error value, in which case it will display 0 instead of the error.

Struggling to see how this will help you find certain text within a cell? Look out for part 2 coming in a fortnight's time as we take this a stage further.