All articles with 'rows()' Tag

Excel Tables have been around for a decade now (they are introduced in Excel 2007), and yet, very few people use them. They are versatile, easy and elegant. At Chandoo.org, we celebrate Tables all the time. If you have never used them, start with below tuts.

• Introduction to Excel tables
• How to use structured referencing
• Tables and Relationships in Excel
• Using lookups and other formulas with Excel tables
• Simple way to get absolute references in Tables
• Customizing table styles for awesome usability

While tables are super helpful, they do come with some limitations. Today let’s examine one such unique problem and learn about an elegant solution.

It seems spreadsheets & dinosaurs on a collision course. How else can you explain Jon’s XKCD Velociraptor problem solved with Excel and now this. Debby, alert reader of our blog sent me this email yesterday.

I need an algebraic formula to solve this in Excel

I have 5 heads, 5 bodies, 4 arm sets, 4 leg sets and 3 tails. I need to see if I can create 1000 dinosaurs from these, and if that’s too many AND I need the 5 digit groupings to prove it and create them.
basically Xa*Xb*Xc*Xd*Xe=1000 – I’m not supposed to go over 1200. […] And then I want the 5 digit combinations if possible – right now they are trying to do the combinations by hand – would be awesome if we could do it in Excel.

Here is a New year gift to all our readers – free 2014 Excel Calendar & daily planner Template.

This calender has,

• One page full calendar with notes, in 4 different color schemes
• Daily event planner & tracker
• 1 Mini calendar
• Monthly calendar (prints to 12 pages)
• Works for any year, just change year in Full tab.

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.

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

Here is a New year gift to all our readers – free 2013 Excel Calendar Template.

This calender has,

• One page full calendar with notes, in 4 different color schemes
• Daily event planner & tracker
• 1 Mini calendar
• Monthly calendar (prints to 12 pages)
• Works for any year, just change year in Full tab.

Not that I have friggatriskaidekaphobia or anything. But since today is Friday & 13th, lets put our Excel skills to test and find out when the next Friday the 13th is going to be.

Here is a new year gift to all our readers – free 2012 Excel Calendar Template.

This calender has,

» One page full calendar with notes, in 4 different color schemes
» 1 Mini calendar
» Monthly calendar (prints to 12 pages)
» Works for any year, just change year in Full tab.

Go ahead and download the FREE 2012 Calendar and Enjoy.

Last year, Steven shared a beautiful Christmas Gift List template with all of us. It is packed with lots of Excel goodness. Just a few days ago, he emailed me another copy of his file with some improvements. So if you are planning for Christmas shopping and want a handy tracker, you don’t want to miss this.

Here is a 2011 new year gift to all our readers – a free 2011 calendar template.

(a little secret: just change the year in worksheet “Full” from 2011 to 2012 to get the next year’s calendar. It works all the way up to year 9999)

You can add notes to individual dates or complete month using the excel template very easily. There are 6 different calendar templates in the download file,

* 4 Yearly Calendar Templates with different color schemes.
* 1 Mini Calendar
* 1 Monthly Calendar (prints in 12 pages)

Go ahead and download the calendar files. Enjoy.

In yesterdays post – Find Quarterly Totals from Monthly Data, we have learned how to use SUMPRODUCT formula to find totals by Quarter from a set of monthly values. The approach is fine, but has one glaring draw back. It only works when you have data for one year. In that post, Martin said, … […]

Here is a simple yet novel use of formulas. Let us say you have a list of values in range A1:A5 and you want to reverse the list. 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 […]

Two charting principles we hear all the time are,

• Sort your data in a meaningful order before plotting it.
• 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.

We all know that bar charts can be used to display values spread across various categories or times and pie charts / donut charts can be used to display percentage breakup of various quantities in a sum total. How about mashing up both to create a Donut Bar chart?

In this tutorial, you can learn how to make a donut bar chart and linking it to a form control to display Product-wise sales breakups spread across several years.

Here is a quick excel formula tip to start your week. Use ROWS() and COLUMNS() formulas next time you need sequential numbers. What does ROWS() excel formula do? ROWS excel formula takes a range as an argument and tells you how many rows are there in that range. For. eg. ROWS(A1:A10) gives 10. How can you […]