Excel

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

Introduction to the new tables in Excel 2010

As the title says this just an introduction. Its aimed at non power users who have recently moved to Office 2010 or 2007 from Excel 2003.

The main reason for this article is to demonstrate the basic use of the new table features and the ribbon features available to users.

I am going to start with showing you how to make a table from existing data and demonstrate a few of the ribbon features. I wont be going into anything too complicated in this post. Depending on feedback I might write some more specific detailed feature articles on table components and using tables later including converting tables back to ranges..

So how do we make a table – its actually very simple. In the following screenshots you will see a fairly typical spreadsheet with some information in it. I will show you how to turn that into a table.

image

Select any cell in the table.

From the Home Tab on the Ribbon click on the Format as Table Button.

jethro's picture

Dynamically refreshing pivot tables when data ranges change

I had a colleague ask me this question today. (edited to protect the innocent – you know who you are!)

I copy data out of E_____ (am sure you have heard of it), and paste it into an excel spreadsheet.  I have a couple of pivot tables hanging off the back of this data.  As this is project data, the longer the project goes on, the larger the dataset gets.  Is there any way in VBA that I can get the pivot table to auto-expand the data set that it is using? 

IMGP7080Here is my answer.

Hey W__

There is a simple solution that doesn’t require any vba at all

First you will need to make a dynamic range name that covers the data being pasted.

jethro's picture

Excel Function of the Week - VLOOKUP & HLOOKUP

I realised that when I wrote the ISNA function article that I had never written an explanation of VLOOKUP. I want to write up explanations for a umber of other functions in the future like COLUMN, ROW, MATCH and INDEX followed by OFFSET. All these make lots of sense when you use them with the VLOOKUP function. So I though it best to start with this function. I will also say that everything I write about VLOOKUP here applies to HLOOKUP as well. the only difference is the orientation, that is VLOOKUP looks across columns from left to right, and HLOOKUP looks down rows from top to bottom.

image

The built in Excel Help is very good at helping with this function. However it doesn't point out many of the pitfalls than occur in common use.

jethro's picture

Excel Function of the week - ISNA

The ISNA function is an Information Function. It is is used to return information about the status of a cell, or specifically another functions results. The most common use I have for this function is to validate the VLOOKUP function. If the VLOOKUP function is looking for a value that can not be found in the lookup range, then it will return #N/A as the result. Thus it is good to wrap that function in an IF Statement using the ISNA function to replace the #N/A results with something else – e.g. a text string such as “error” or maybe a zero.

=IF(ISNA(VLOOKUP(A1,$B:$B,1,FALSE))=TRUE,"error",VLOOKUP(A1,$B:$B,1,FALSE))

clip_image001

Remember the ISNA function returns a TRUE or FALSE. These equate to a 1 or 0 so can be used in formulas such as array formulas as well.