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.
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
Quickly Fill Blank Cells in a Table [Reader Tip]
This post is authored by Martin, one of our readers.
Situation:
Sometimes I encounter data in my tables with blank cells where there is a repeated value from the cell directly above. See below:
This can be annoying when it comes to interpreting the data and when sorting columns.
Solution:
To know how I solve this, just read rest of the article.
10 Excel Keyboard Shortcuts I can’t live without!
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.
Quickly Remove Data Validation Rules using Paste Special
Here is a quick tip on removing data validation rules. We all know that data validation is a powerful feature in Excel to control what data is entered. But, sometimes, you may want to remove the rules from a set of cells. How would you do it? The normal approach is, Select the cells with […]
220 Excel Tips, Tutorials, Templates & Resources for You [Celebrating 20k RSS Members]
I have an exciting news & massive post for you.
Chandoo.org reaches 20,156 RSS Subscriber mark on Jan 19, 2011As of Jan 19, 2011, our little blog has registered our 20,000th RSS Subscriber. While this is not a huge achievement or anything, It certainly calls for celebration. I am so happy to see our mission to make people awesome in Excel is reaching out to more people everyday. Thank you.
To celebrate this milestone, I am doing a massive post with 220 Excel tips, tricks, tutorials & templates.
Formulas [52 tips]
Formatting & Conditional Formatting [36 tips]
Charting [60 tips]
Tables & Pivot Tables [15 tips]
Using Excel [47 tips]
Free Downloads [5 tips]
Recommended Resources [5 tips]
How to Paste Numbers Only [quick tip]
Sometimes when you are working with data, you may need to just copy and paste numbers from one range to another. Here is a handy little trick to achieve that.
Use Paste Special > Add operator to paste numbers only.
1. Select the source range, press CTRL+C
2. Now, go to target range, press ALT+ES to activate paste special dialog
3. Select “Add” operator (you can press d)
4. Click ok.