All articles with 'transpose' Tag

Transpose this address data [VBA homework]

Published on Jan 16, 2016 in Excel Challenges, Power Query, VBA Macros
Transpose this address data [VBA homework]

Here is an interesting problem to keep you busy.

Transpose the address data in column A into the format indicated in C:G using either VBA, formulas or Power Query. Once done, post your answers in comments section.

Continue »

3D Max Formula for Excel

Published on Sep 9, 2014 in Excel Howtos, Learn Excel
3D Max Formula for Excel

We all know about the MAX formula. But do you know about 3D Max?

Sounds intriguing? Read on.

Lets say you are the sales analyst at ACME Inc. Your job involves drinking copious amounts of coffee, creating awesome reports & helping ACME Inc. beat competition.

For one of the reports, you need to find out the maximum transactions by any customer across months.

But there is a twist in the story.

Your data is not in one sheet. It is in multiple sheets, one per month.

Continue »

Quickly combine text in multiple cells using this trick! [Formulas]

Published on Jan 13, 2014 in Excel Howtos
Quickly combine text in multiple cells using this trick! [Formulas]

Ever wondered how to go from a bunch of cells with text to one big combined text? Like shown above.

Well, there is a simple trick. One of our readers, Grant shared this with us. Read on to learn this.

Continue »

How to transpose a values in a row to column using formulas… [Quick tip]

Published on May 14, 2013 in Excel Howtos
How to transpose a values in a row to column using formulas… [Quick tip]

This is interesting, I am in Columbus to meet one of my college friends. I remember him as a very meticulous person from college days. So it is no surprise when he showed me his massively impressive finance tracker last night. He has been tracking expenses, income, credit card payments and gas (petrol) consumption since 2008. Very impressive indeed.

Then out of blue he said, he has a problem with his spreadsheet. In this own words,

When entering data for credit cards, I use one column per card. But in my report view, I want to show credit card details in rows. How do I do this?

Something like above…. Today, lets learn how to do this using Excel formulas.

Continue »

Transpose a table quickly using Copy – Paste [Quick tips]

Published on Feb 4, 2013 in Excel Howtos
Transpose a table quickly using Copy – Paste [Quick tips]

On Friday, we learned how to transpose a table of data using Excel formulas. Today lets learn a quicker & easier way to do this by just using copy, paste, find & replace.

Continue »

Transpose a table of data using Excel Formulas

Published on Feb 1, 2013 in Excel Howtos
Transpose a table of data using Excel Formulas

Today lets tackle a familiar data clean-up problem using Excel – Transposing data.

That is, we want to take all rows in our data & make them columns. Something like this:

Learn these 4 techniques to transpose data:
1. Using Paste Special > Transpose
2. Using INDEX formula & Helper cells
3. Using INDEX, ROWS & COLUMNS formulas
4. Using TRANSPOSE Formula

Continue »

Evolution of Privacy Policies on Facebook – a Panel Chart in Excel

Evolution of Privacy Policies on Facebook – a Panel Chart in Excel

Out of curiosity I took the data from Matt McKeon’s Privacy on Facebook chart and try to re-do the chart in Excel. I made a panel chart depicting how Facebook’s privacy policies have changed since 2005. You can see a bigger version of chart as well as get the download excel from the post. Read on…

Continue »

Transpose Excel Rows and Columns [Quick Tip + Video]

Published on Nov 18, 2009 in Excel Howtos, Learn Excel
Transpose Excel Rows and Columns [Quick Tip + Video]

Transposing rows and columns in a table is one of the most useful tricks when you are pasting data. Yet, it always surprises me that very few people actually know this. So here it is…

Continue »

Switch Rows and Columns in Charts [Quick Charting Tip]

Published on Nov 2, 2009 in Charts and Graphs, Excel Howtos
Switch Rows and Columns in Charts [Quick Charting Tip]

Let us say you have built a nice chart showing your sales and profits for the top 5 products (learn how to highlight top 5 products in a list), with products on X axis. Suddenly your boss wants to switch the rows to columns (or transpose the chart) so that she can see metric level grouping instead of product level grouping. No need to freak out and rush to Espresso machine, You can do it very easily with Excel Charting features.

In today’s quick tip you will learn how to swap chart rows and columns in excel.

Continue »

Member of month, Excel links and Cooked HDD

Published on Aug 11, 2009 in blogging, excel links
Member of month, Excel links and Cooked HDD

Starting this month I will announce one member of our little community as member of month. It is to honor the contribution they made.

Jeff Weir is our member of month for July, 2009. He not only commented more than 40 times in the last month, but he even wrote a marvelous guest post on the chart busters series. I have learned several valuable excel and charting tips from him in the past few weeks. I am sure some of have too. Thank you Jeff.

Also in the post we have some excel links worth checking.

Continue »