functions

  • 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

Excel Function of the Week - SUMIF

Possibly one of the most powerful easy to use formulas in Excel is the SUMIF formula.

It is very easy to use once you get your head around its syntax and it is extremely useful in all sorts of scenarios.

Here is the Microsoft Excel Explanation.

You use the SUMIF function to sum the values in a range (Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5.

Syntax SUMIF(range, criteria, [sum_range])

I will explain that below more simply.

First let me explain some common scenarios where you might use SUMIF. Hopefully from there you can envisage similar scenarios for your own circumstances.

You have a list of names and values – maybe pays or scores or totals of times you beat them in darts matches or numbers of volunteers in each division or county. When the names appear more than once – e.g. you might have several different staff categories in each store across each state as per the example below.

image

Lets say you want to sum the total number of Part Time Employees in South Australia across all stores.

I am going to show you a summary table below this one that provides all the results for each staff category for each State and then explain how this was done using one simple formula. An understanding of ABSOLUTE and RELATIVE references is important.

jethro's picture

Link Round Up

Time for a link dump from my browser – clearing out tabs.

2010-07-31 Strobist Shoot Toorbul 162Education

How to Create a Portfolio with Evernote (Education Series) – Evernote is an awesome app – We use it all the time on various android and windows platforms.

Social Marketing and Web Development

Building trust - The Connection Constant

Want to build a online store? Choosing a payment gateway

Facebook – how to convert your personal account into a Facebook Page - for all those people who created a “person” for their business page.

More how to convert Facebook profiles into business pages including tricks and traps

Drupal

Configuring and adding images to Drupal 7 content with Wysiwyg, IMCE and Lightbox2 – very good tutorial

Excel

Probably the best explanation of using INDEX function for non volatile dynamic range creation in Excel

Windows 8

There will be lots coming on Windows 8 on this blog later – for now heres a very good read about how Windows 8 can pool data storage

Business

Realities of Business is a new website by a great friend to promote his new book. Worth a read! Buy it now!

Photography

Two local photographers worth checking out.

Helen Graham

Dale Travers

jethro's picture

Round up of Excel Tips and Hints

As Office 2010 becomes more prevalent in the workplace (most of our clients either have or will be soon updating to it) so the use of the ribbon has become more natural and intuitive for people. It is certainly much more intuitive than the old menu driven interface.

rh2-150x150Play a fun game called Ribbon Hero 2 that allows you to learn the fastest and best ways to do various common tasks in the various Office Apps. My kids are using this to learn the interface and some of these common tasks. I have even learnt some things in the 5 minutes I have spent playing it so far! Download Ribbon Hero 2.

Take the first step in growing your Excel Skills. Microsoft have created a series of learning videos that assist new users to Excel to grow their skills

Learn how to create your own Excel Macros with a free training course. Ideal for the entry level person who wants to explore macro writing for the first time. Note this doesn't create efficient code, but its a start to understanding how the VBA code interacts with Excel.

PowerPivot for Excel - if you have looked or used PowerPivot (See my review of Power Pivot) than this page has some good links for additional resources.