Excel 2010 has several improvements compared to earlier version – Excel 2007. In this post, I want to highlight some of the User Interface improvements made in Excel 2010 that are very exciting and fun to use.

Preview before pasting

This is another cool feature in Office 2010. When you have some data to paste, now you can preview the paste live before choosing an option. See the illustration to understand how this works.

Collapse Ribbon using a button

Now you can collapse the ribbon using this simple button. No need to double click on ribbon menus to collapse the ribbon.

Bye bye Office button, welcome back “File” menu

With excel 2010, MS is bringing back the “file” menu. When I started using excel 2007, it took me a week to get used to the office button. Now, thankfully the file menu is back.

Double click on chart items to format them

With Excel 2010, you can again click on chart items (like labels, data series, axis, titles etc.) to open the format dialog. This is a nice improvement. Of course, the dialogs are still 2007 like.

Search inside filters (oh, this feels recursive)

When you set auto filters in excel 2010, now you can use a little search bar inside the filter to select the items you want to filter. This makes life lot more simpler for those of you aksing questions like “so how many are from alabama?”

Tables show filters even when you scroll down

Another interesting improvement is that when you make a data table in excel 2010, you will see filters and sort options even when you scroll down.

Excel 2010 UI less flashier than 2007

Excel 2010 UI looks considerably better and less stressful than 2007. The colors are dull and subtle. The icons don’t call for attention unless you want to do something. The menus / ribbons feel smoother and slicker. [Learn to use Excel Ribbon with this Free e-Book]

All new print previews

The print previews are now part of backstage. Printing seems much more easier with excel 2010 than earlier versions.

You can customize ribbon

With Excel 2010, you can customize the ribbon very easily (you can do that in Excel 2007 too, but it is bit more intricate). Just go to Excel options and select “customize ribbon” option. You can make your own ribbon menus and add the buttons / tools you prefer.
[Originally this point read "you can select multiple objects..." but as my good friend Jon pointed, it was a mistake. So I have deleted that part and added a new improvement]
You can select multiple objects using mouse, again
In excel 2003 and earlier, if you had to format multiple objects (like charts, drawings, clip arts etc.) you could use the “select objects” from drawing toolbar (the icon that looks like a mouse pointer). Sadly, MS removed this feature in Excel 2007, so to format multiple objects, we had to manually click on each object while holding SHIFT key. Now, the select objects is back. This can be a time saver if you work with several charts or shapes at a time.

Slicers to filter pivot tables with ease

And of course, the much touted slicers. Slicers are easy to use and make analysis more fun. See the demo.

Have you tried Excel 2010 ?

Did you try excel 2010? If not download it from MS site. Give it a try and let us know what you think about it.
What are the features that you liked / hated?

I have asked our customers to share their feedback and this is what they say:

Project Management Templates – the future:

• New Templates:
  At least 5% customers have asked me to include more gantt chart templates. I also have plans to include templates for RACI matrices, training plans, document change trackers (VBA based).
• Training Videos:
  I have been working on a set of training videos to help our customers make the best of these templates. The videos will be short and teach you how to use the templates.
• Upgrade coming after Christmas:
  I am planning to release an upgrade after Christmas with newer templates. All of you who bought the templates earlier will get a free upgrade (why? because we are awesome like that!).

So go ahead and download your copy of project management templates today.

Do you have a question or feedback?

Are you considering the project management templates but not sure if you should buy them? Do you have any questions or some feedback that you want to share with us? Please drop a comment or send me an e-mail at chandoo.d @ gmail.com. While I cannot promise to include every feature that you want, I will definitely use your inputs when releasing upgrades.

Thanks to all of you who bought the templates

I wish you more success in your projects and career.

In an empty cell write =INDEX($A$1:$A$5,6-ROWS($A$1:A1)) and copy down.

Boom, you get the reversed list.

Here is how the formula works:

• In the reversed list, first item is last item in the original list (ie 5th item in our case).
• INDEX() formula takes a list, a row number (and optional column number) and returns the value at intersection.
• In this case, $A$1:$A$5 is the list.
• 5 is the size of list.
• ROWS($A$1:A1) gives running numbers from 1 thru 5 when copied in any range of 5 cells. Read more on using ROWS() formula.

More on lists: Shuffle a list of numbers | Sort a list of texts using formulas |  Remove duplicates from a list

Make sure you select the “custom” installation option and choose to “keep the old versions”, otherwise the beta will upgrade your office installation. (Beta installation expires by October 2010)

Here is a brief list of new features in Excel 2010, most of them are pretty exciting.

• You can make sparklines and microcharts in Excel 2010
• Pivot tables are lot nicer and cooler to use – There is a free PowerPivot add-in from MS to help you make dashboard like pivot-tables instantly.
• You can make your own ribbons with the buttons you want, I am guessing this will be a huge productivity booster (compared to Excel 2007).
• The Office button is replaced with a “File” menu that opens the backstage, where you can do a ton of different file related things. Very clean and very easy to learn.
• Slicers feature using which you can easily filter pivot report data (or data from other connections)
• Conditional format improvements – like new incell charts, custom icons etc.
• Several usability enhancements to Excel Tables, Data Filters, Pivot Tables etc.
• While pasting data, you can preview various options (including paste-special options) and decide what to do.
• Overall, the interface feels lot lighter, smoother and faster – just like windows 7.

Go ahead and give it a try and let us know what you think of Excel 2010.

To transpose a table of data from rows to columns (or columns to rows)

1. Select the table, press CTRL+C
2. Goto an empty cell
3. Right click and select Paste Special (alternatively press ALT+E followed by S)



4. Select “Transpose” check box, it is above the OK button.
5. Click Ok. Transposed your data is.

I have made a 15 second tutorial on this. See it below:

(Watch the tutorial on youtube, you can also see it in HD)

15 more tips on pasting data in excel | Transpose Charts | More Quick Tips | More Excel Video Tutorials

Let us say you have a sales transaction database like this:

To show “Total units sold by salesperson in each month” in pivot table

1. First make a pivot table [video tutorial] with Sales person as column header, Transaction date as row header and “total units” as pivot values.
2. Now select any transaction date, right click and select “Group”
3. In the group dialog box, select “Months” for group by option. Press ok.
4. Presto! your data is grouped by month. No more than 3 seconds and you are ready analyze the data by month and find trends.

I have made a small screen-cast to show how this can be done. See it here:

Special cases for grouping dates in pivot tables

While the above example is simple, there are various things you can do when you are grouping data in pivot reports. Here are some special cases and how to get the grouping in pivot tables.

Group by Quarter & Month:

To group the pivot tables by Quarter and Month,

1. Select “group” option.
2. Select both “month” and “quarter” in the “group by” option, Click ok.

Group by Week:

To group the pivot table dates by week,

1. Select “group” option
2. Select “day” in the “group by” option. When you do this, the “number of days” box will be enabled. Enter the number of days as “7″ to group your pivot report by week.

Group by Hour of the day:

Assuming your data has dates along with the actual transaction time, you might want to analyze the sales by hour of day, to find out say “if one product is selling more than other during certain hours”.

To group the pivot table dates by hour of the day:

1. Select “group” option
2. Select “hour” in the “group by” option.

Collapsing & Expanding Pivot Table Groups:

When you group pivot reports by more than one “group by” you will see a little icon with + or – sign to expand or collapse the groups. Using this feature, you can easily deep dive in to a particular group to do further analysis. For eg. you can collapse all quarters and just expand Q2 to understand why the sales went up.

Another useful feature of these collapse / expand buttons in pivot tables is that, when you make a pivot chart, the collapsed groups are collapsed in the pivot chart too. And it is dynamic, ie, if you expand a group in the pivot table, the chart gets updated and shows more details.

Tell us how you use the group / un-group feature in pivot tables?

I am finding numerous possibilities with the group / un-group feature of pivot tables. I will learn new things and share them with you as we go along. Meanwhile, share your tips, experience and ideas using comments. I would love to learn from you.

Related Articles on Pivot Tables and Dates:

I suggest reading beginners guide to excel pivot tables, working with dates & times in excel.

Moving on to this weeks excel and charting links worth checking.

Why gradient fills can be bad for your charts?

Tony and Support Analytics analyzes a chart featured in Baltimore Sun and tells us why it is bad. The chart uses gradient fills along with a non-zero axis start point. This makes the values look different than they are. Tony wastes no time in pointing out the mistake and teaches us an important charting lesson.

Charting when you have lots of data – Unemployment Data Case Study

This is a old one, but I am not sure how I missed it. Jorge shows us how to make charts when you have lots of data. Very effective. Go ahead and read the article, there are few good examples.

VBA Macro to automatically back up excel files

JP at Code for Outlook and Excel blog releases a new version of automatic excel backup macro. The macro silently backups excel files and saves you any trouble in case of you made a mistake and need to backtrack.

Mimicking Slicer feature in Excel 2007 and earlier

In excel 2010, there is a new powerful data analysis feature called Slicers [more on MS site] Andrewe explores this feature and gives us a DIY Excel Slicer workbook for those of us using earlier versions of Excel. It is a fun way to explore data, go ahead and check it out.

Copying Vlookup Formulas – the easy way

Mike at datapig talks about a simple yet very effective formula trick for those of us working on lots of data and using vlookups all the time. He points out that using either COLUMN() or COLUMNS() formulas can help you easily drag the vlookup formulas to get values from other columns as well.

Wish you a happy week ahead.

PS: Do you want to share an excel or charting resource with our community? Tip me at chandoo.d @ gmail.com.

What is the the most embarrassing charting miskate your made?

For me it has to be that one time when I made a sports dashboard using excel. I have adjusted the axis scale of a bar chart so that my favorite cricket player (Sachin Tendulkar, who else?)’s records are emphasized. In a matter of minutes I have received several comments from all over world pointing out the mistake. Even though, the intention was to highlight the achievements of master blaster, the axis adjustment was obviously a mistake.

Soon I have realized the mistake and corrected it in a follow up post where I proposed alternative visualizations for test cricket statistics. Heck, we even had a poll where I asked you to tell if “is it ok to have axis of bar charts start at non-zero value“.

What about you? What is your most embarrassing charting moment?

Share with us using comments, because we learn better from our mistakes.

Related:

• Is your chart’s underwear showing?
• Are you making blow charts?
• More examples of bad charts

I couldn’t be happier to see that mail. Zoho is a perfect sponsor for a contest like this. If you haven’t heard of it, Zoho is a very popular online office solution. They have a collection of over 20 different productivity applications including zoho sheet, writer, show, projects, CRM, reports and much more. Check out more about online reporting and BI services on the Zoho Reports page.

What are you waiting for? Go ahead and take part in the contest, download the data and get charting.

• Sort your chart data in a meaningful order.
• Show only relevant information, not everything – because un-necessary information clutters the chart.

Today we will learn a dynamic charting technique that will mix these two ideas in a useful way. I call this a Top X chart.

Note: This article uses the concepts from How to make chart data ranges dynamic. I suggest reading that article first if you haven’t.

What in the name of 3d square pie is a TopX chart?

A top-x chart is an interactive (or dynamic) chart which automatically sorts the data from top to bottom and displays only TOP ‘X’ items and showing the remaining balance as the last item. Users can adjust the value of ‘X’ and chart will be re-drawn to show more (or less) values.

See this example implementation:

How to make a Top X chart using Excel – 5 Step Tutorial

1. Have your data ready

It should be in 2 columns – first column – the attribute (for eg. customer name) and second column – the value. Lets assume the data is in range A1:B10.

2. Add 3 dummy columns

We need to add 3 dummy columns to this list. (you can do away with dummy columns if the list is sorted).

1. First dummy column – to make the values unique. We just take the value in column B and make it unique in Column C by adding a small incremental fraction to it. Something like =B1+10^-6*ROWS($B$1:B1) will do. [Help on ROWS formula]
2. Second dummy column – to get first X sorted customer names.
3. Third dummy column – to get first X sorted sales values. We use LARGE excel formula [14 more powerful excel formulas] for both these columns.
   It is your home work to figure out how to write these formulas.

3. Find a cell where user can input the X

Lets call it $F$2.

4. Update the dummy column formulas

We need to update the formulas in dummy columns 2 & 3 so that we can show “all remaining customers” as well.
To Do this, you can add an IF formula that would check if the number of the customer is >X and then just show “All remaining” with the sum of remaining values. Remember, your IF formula should be smart enough to show empty values if the row number is >X+1.

At this point, the data table should look something like this for X=5

5. Finally, select Dummy column 2 and 3, make a chart

We will re-visit our tutorial on how to make charts with dynamic ranges of data. We use the same concepts to make this interactive top x chart.

So make a named range pointing to the result of an OFFSET formula. If this sounds like turkish, I suggest getting a cup of coffee and reading the charts with dynamic ranges post. Now.

Once you have created the named range, just insert a new chart and use the named ranges as data sources. Format the chart a bit if needed and you should have a Sparkling Top X Chart, ready to fly.

Why Top X charts are cool?

• Top X charts let users play with them and find what they want. They are better than static versions.
• The show the necessary while hiding the rest.
• They show data in sorted order, which is awesome.
• You can easily build up on this concept to make them more presentable / fun. For eg. you can add a slider control and point it to cell F2.

Go ahead and download the Top X chart Template

Click here to download the topx chart template [Click here for Excel 2007 version, it is even more awesome] Play with it to learn how the formulas are working.

This is a slightly complicated chart, so beginners, you may want to jump around PHD and to get a grip on the key concepts.

What are your views on Top X Chart?

Please share your ideas and implementations suggestions using comments. I *love* to hear what you think about this.

Other Charts you can try:

Check out some of the excel dynamic charts to get inspired.