All articles with 'excel tables' Tag

Last week, we had a lovely poll on what are your favorite features of Excel? More than 120 people responded to it with various answers. So I did what any data analyst worth his salt would do,

I analyzed the data and here are the top 10 features in Excel according to you.

Read on to learn more.

HR managers & department heads always ask, “So what is the vacation pattern of our employees? What is our average absent rate?”

Today lets tackle that question and learn how to create a dashboard to monitor employee vacations.

What do HR Managers need? (end user needs)

There are 2 aspects tracking vacations.

1. Data entry for vacations taken by employees
2. Status dashboard to summarize vacation data

When analyzing business data like sales, shop visits or productivity, one of the questions managers always ask is,

What is the best month / week for this data?

To answer this question, we need to make a chart that looks like above. Today, lets learn how to highlight portions of such charts that correspond to best week or best month.

Zebra lines, the dull highlighting applied to alternative rows is a very good way to make your tables readable & pretty.

We can use either conditional formatting or table formats to quickly add zebra lines to our data.

But what if you want a little more?

What if you want to highlight, lets say 3 rows in one color and 3 in another and repeat this …

Today, lets learn OFFSET formula.
OFFSET formula gives us reference to a range, from a given starting point with given height and width in cells.

OFFSET formula syntax
OFFSET formula looks like this:
=OFFSET(starting point, rows to move, columns to move, height, width)

Starting point: This is a cell or range from which you want to offset
Rows & columns to move: How many rows & columns you want to move the starting point. Both of these can be positive, negative or zero. More on this below.
Height & width: This is the size of range you want to return. For ex. 4,3 would give you a range with 4 cells tall & 3 cells wide.

Read on…,

Finally, I got some time to sit down and do what I love most – write a blog post to make you awesome in Excel. After a whirlwind trip to Sydney, I am back in India to spend few days with my kids & wife before rushing to Australia to run 2nd leg of my training programs (in Perth, Melbourne & Brisbane). I did 2 sessions in Sydney – one for KPMG and other for public and both went very well. We got lots of positive feedback and people really loved it. I am saving the details for another post, but today lets talk about Interactive Sales Chart using Excel.

Take a look at the Interactive Sales Chart

First, take a look at interactive sales chart. Today, you will learn how to build this using Excel.

Excel formulas acting slow? As part of our Speedy Spreadsheet Week, today lets talk about optimizing & speeding up Excel formulas. Use these tips & ideas to super-charge your sluggish workbook. Use the best practices & formula guidelines described in this post to optimize your complex worksheet models & make them faster.

1. Use tables to hold the data
2. Use named ranges & named formulas
3. Use pivot tables
4. Sort your data
5. Use manual calculation mode

… and more. Read on to learn these top 10 tips & ideas to improve performance of your excel formulas.

We can take any Excel workbook and format it until Christmas, and we would still not be done. But not many of us have so much of time or energy. So, today, lets talk formatting.

In this, you will learn how to
1) Use tables to format data quickly
2) Change colors of your worksheet in a snap
3) Use cell styles
4) Quickly clone formatting using format painter
5) Clear formats to begin with a clean-slate
6) Formatting shortcuts
7) Formatting options for print
and 8 ) Why you should not go overboard formatting

So go ahead and become a formatting pro.

Everyone and their colleague knows the importance of Excel keyboard shortcuts. While it may be a good idea to know a 97 different shortcuts for everything in Excel, the fact is, you don’t use them all the time. So, it all boils down to memorizing the few shortcuts that you use all the time.

In this post, I am sharing 10 such shortcuts that I can’t live without, especially, when I am analyzing data, making charts and formatting my workbooks.

With Excel 2007, Microsoft has introduced a powerful and useful feature called as Tables. One of the advantages of Tables is that you can write legible formulas by using structural references. That means, you can write easy to understand formulas like this,

But, there is a problem. When you write these formula and drag the formula cell sideways to fill remaining cells, Excel changes table column references and thus makes your formulas almost useless.

Well, there is a simple workaround for this problem

Situation

So far we have seen what VLOOKUP formula is and how to put it to some nifty uses. Today, we will go one step further and learn how to do 2 Way Lookups.

What is a 2 Way Lookup?

Lookup is when you find a value in one column and get the corresponding element from other columns. 2 Way Lookup is when you lookup value at the interesection corresponding to a given row & column values.

For example, assuming you have data like below, and you want to findout how much sales Joseph made in month of March, you are essentially doing a 2 way lookup.

Read more to find how to solve this.

Excel Tables, a newly introduced feature in Excel 2007 is a very powerful way to manage & work with tabular data. I really like tables feature and use it quite often. If you are new to tables, read up Introduction to Excel Tables.

In this short video tutorial I explain how to combine VLOOKUP, INDEX, MATCH formulas with Excel Tables.

Ok, since excel school 3rd batch is going to open on 15th, I wasnt going to write anything today. I have slept just 4 hours last night, blame it on work (and that funny video on youtube). But I found 30 minutes free time, so here you go, a quick but delicious tip on making your data validation dynamic.

Dynamic Data Validation?!? What in the name of slice bread and peanut butter is that?

See the demo aside to understand and read on to master the trick.

As part of Excel 2010, Microsoft has introduced an exciting and new charting feature called as “sparklines”. Edward Tufte coined the term sparkline and defined it as, “intense, simple, word-sized graphics”. I think adding sparklines to Excel’s pack of visualizations is a huge step in the right direction. Sparklines (often called as micro-charts) add rich visualization capability to tabular data without taking too much space. While MS’ implementation of Sparklines in Excel 2010 leaves a lot to be desired, it provides a solid platform and introduces this powerful charting idea to masses.

In this post, we explore sparklines in excel 2010 and learn how to use them. You can find several tips and ideas on implementing sparklines in your upcoming report.

Office 2010, the latest and greatest version of Microsoft Office Productivity applications is going to be available worldwide in the next few weeks. I have been using Office 2010 beta since November last year and recently upgraded my installation to the RTM version. I was pleasantly surprised when I ran Microsoft Excel 2010 for first time. It felt smooth, fast, responsive and looked great on my comp.

This week, I want to celebrate the launch of Excel 2010 and write about the following new features in Excel 2010.

Also, you can win a copy of Office 2010 – home & student edition by leaving a comment on this or any of the other excel 2010 posts scheduled for this week