All articles with 'rows()' Tag

Relative References in Excel Tables

Published on Apr 21, 2017 in Excel Howtos, Learn Excel
Relative References in Excel Tables

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.

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.

Continue »

Make 1,200 dinosaurs in no time with Excel [formulas]

Published on Jan 29, 2016 in Learn Excel
Make 1,200 dinosaurs in no time with Excel [formulas]

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.

Continue »

Free 2014 Calendar, daily planner templates [download]

Published on Jan 2, 2014 in Learn Excel
Free 2014 Calendar, daily planner templates [download]

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.
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 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 »

2013 Calendar – Excel Template [Downloads]

Published on Dec 26, 2012 in Learn Excel
2013 Calendar – Excel Template [Downloads]

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.
Continue »

Finding Friday the 13th using Excel (and learning cool formulas along way)

Published on Jan 13, 2012 in Formula Forensics, Learn Excel
Finding Friday the 13th using Excel (and learning cool formulas along way)

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.

Continue »

2012 Calendar – Excel Template [Downloads]

Published on Dec 27, 2011 in Learn Excel
2012 Calendar – Excel Template [Downloads]

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.

Continue »

Christmas Gift Shopping List Template – Set budget, track your gifts using Excel

Published on Nov 23, 2011 in excel apps, Learn Excel
Christmas Gift Shopping List Template – Set budget, track your gifts using Excel

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.

Continue »

2011 Calendar – Excel Template [Downloads]

Published on Dec 17, 2010 in Learn Excel
2011 Calendar – Excel Template [Downloads]

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.

Continue »

Quarterly totals when you have multi-year data [SUMPRODUCT again]

Published on Apr 30, 2010 in Excel Howtos, Learn Excel
Quarterly totals when you have multi-year data [SUMPRODUCT again]

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, … […]

Continue »

Reverse a List using Formulas [Using Excel INDEX() Formula]

Published on Nov 19, 2009 in Excel Howtos, Learn Excel
Reverse a List using Formulas [Using Excel INDEX() Formula]

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 […]

Continue »

Top X chart – Show Top X values of a chart Interactively

Published on Nov 12, 2009 in Charts and Graphs, Learn Excel
Top X chart – Show Top X values of a chart Interactively

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.

Continue »

Recipe for a Donut Bar Chart

Published on Sep 30, 2009 in Charts and Graphs, Learn Excel
Recipe for a Donut Bar 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.

Continue »

Use ROWS() and COLUMNS() formulas to generate numbers in a sequence [quick tip]

Published on Aug 17, 2009 in Learn Excel

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 […]

Continue »