All articles with 'Learn Excel' Tag

Excel School Closing in a Few Hours – Join Now!

Published on Sep 29, 2010 in Charts and Graphs, products
Excel School Closing in a Few Hours – Join Now!

As some of you know, I run an online Excel Training Program called as Excel School. I have opened registrations for 3rd batch of this program on September 14th. Thank you very much for supporting this program wildly. In a few hours, I will be closing the registrations for Excel School.

Click here to sign up for Excel School

Read the rest of the post to learn more about Excel School and also download time across world chart template.

Continue »

Show Zebra Lines when Value Changes [Excel Conditional Formatting Homework]

Published on Sep 28, 2010 in Featured, Learn Excel
Show Zebra Lines when Value Changes [Excel Conditional Formatting Homework]

Here is a quick home work on excel conditional formatting. Lets say you have data as shown below to left and you need to show zebra lines whenever the value changes (see right). Your home work is simple. Just figure out how to write conditional formatting rules to add zebra lines. The data set is […]

Continue »

Remove duplicates & sort a list using Pivot Tables

Published on Sep 27, 2010 in Analytics, Learn Excel, Pivot Tables & Charts
Remove duplicates & sort a list using Pivot Tables

Removing duplicate data is like morning coffee for us, data analysts. Our day must start with it. It is no wonder that I have written extensively about it (here: 1, 2, 3, 4, 5, 6, 7, 8). But today I want to show you a technique I have been using to dynamically extract and sort […]

Continue »

Displaying & Selecting a Scenario using VBA [Modeling in Excel]

Published on Sep 24, 2010 in Financial Modeling, VBA Macros
Displaying & Selecting a Scenario using VBA [Modeling in Excel]

If you are building financial models or any other type of excel based decision models, chances are, there will be multiple scenarios in your model. Whenever you have multiple scenarios, you may want an easy, intuitive way to select one of them. In this post, I will present an interesting scenario display & selection technique that I received by email from our reader Itay Maor.

Continue »

How I Analyze Excel School Sales using Pivot Tables [video]

Published on Sep 22, 2010 in Charts and Graphs, Pivot Tables & Charts

Some of you know that I run an online excel training program called Excel School. If you want to join, click here. Only 8 days left.

I run excel school mainly to meet new students, understand their problems and learn new ways to solve them. But, Excel School also presents me with an interesting analytics challenges. In this post, I will share 2 pivot table based analytic techniques I used just yesterday to answer few questions I had about Excel School sign-ups.

Watch this 15 min. video to see how I analyzed the data

Continue »

Putting it all together – Final Project Evaluation Model [Part 6 of 6]

Published on Sep 21, 2010 in Financial Modeling, Learn Excel

This is a guest post written by Paramdeep from Pristine. Chandoo.org is partnering with Pristine to bring an excel financial modeling online training program for you. This is Part 6 of 6 on Financial Modeling using Excel In this tutorial we are going to learn how to build assumptions & input sheets in our excel […]

Continue »

Show all the names & their References [Spreadsheet Audit]

Published on Sep 20, 2010 in Excel Howtos
Show all the names & their References [Spreadsheet Audit]

We all have been there. You started to build a simple workbook to keep track of an ongoing project or dashboard or something equally complicated. Even before you realize the workbook has 23 sheets and 41 named ranges all going from one place to another, like flying spaghetti monster, only less awesome. Now, how do […]

Continue »

A round-up on Circular References

Published on Sep 16, 2010 in Learn Excel
A round-up on Circular References

Here is a little experiment to freak out excel.

Go to cell C3 and write =C3 and press Enter. Excel would throw up nasty message saying, “Microsoft did not know what to do. We have a sent a support engineer to your home, but he is stuck at the round-about near your house.”

Well, not really. But what you did when you wrote the formula =C3 in cell C3 was, you created a circular reference.

A circular reference is created when you refer to same cell either directly or indirectly.

See Mona Lisa, in circular reference style.

Learn more about Excel Circular References, how to use them, examples, how to avoid them, how to deal with them in this article.

Continue »

Excel School is Open – Join Today

Published on Sep 15, 2010 in Learn Excel, products
Excel School is Open – Join Today

Hello reader, my friend & supporter,

I am happy to announce that Excel School is open and ready for registrations.

Visit Excel School page to learn more and sign-up.

In this post, you can find some information about the program and links to sign-up.

Continue »

Make your data validations dynamic! [quick tip]

Published on Sep 13, 2010 in Excel Howtos
Make your data validations dynamic! [quick tip]

Ok, since excel school 3rd batch is going to open on 15th, I wasnt going to write anything today. I have slept just 4 hours last night, blame it on work (and that funny video on youtube). But I found 30 minutes free time, so here you go, a quick but delicious tip on making your data validation dynamic.

Dynamic Data Validation?!? What in the name of slice bread and peanut butter is that?

See the demo aside to understand and read on to master the trick.

Continue »

How to Calculate Working Hours Between 2 Dates [Solution]

Published on Sep 10, 2010 in Learn Excel
How to Calculate Working Hours Between 2 Dates [Solution]

This post builds on earlier discussion, How many hours did Johnny work? I recommend you to read that post too. Lets say you have 2 dates (with time) in cells A1 and A2 indicating starting and ending timestamps of an activity. And you want to calculate how many workings hours the task took. Further, lets […]

Continue »

How to Paste Numbers Only [quick tip]

Published on Sep 8, 2010 in Excel Howtos, Learn Excel
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.

Continue »

August 2010 – Best Month Ever (and 2 charting tips inside)

Published on Sep 3, 2010 in blogging, Charts and Graphs
August 2010 – Best Month Ever (and 2 charting tips inside)

Time to stop everything we do and celebrate, for, our little community at chandoo.org had the most fantastic, awesome month ever.

That is right. August 2010 has been the best month since I started chandoo.org. We have broken a majority of previous records in terms of conversations, connections, content and revenues.

In August, we had 17 posts, with 604 comments. We had 178,000 visitors reading 453,000 pages. Our RSS subscriber base grew to 11,917 (it was 5738 an year ago). August has been the best month in-terms of revenues too. We sold highest number of project management templates & excel formula e-books since launch. We had a stellar launch for wedding planner with 22 customers.

Read rest of this post to find statistics about chandoo.org and get 2 charting tips.

Continue »

Modeling & Building Cash-flow Projections for Project Valuation [Part 4,5 of 6]

Published on Sep 2, 2010 in Financial Modeling, Learn Excel
Modeling & Building Cash-flow Projections for Project Valuation [Part 4,5 of 6]

In the 4th and 5th part of our financial modeling series, we will learn how to model and build cashflow projections for project valuation.

We will understand the basics behind cash-flow modeling, what kind of non-cash expenses should be added back to get the free cash-flow. You can also get the blank and completed cash-flow projection worksheets to practice these techniques.

Continue »

How many hours did Johnny work? [Excel Homework]

Published on Aug 27, 2010 in Excel Howtos, Learn Excel
How many hours did Johnny work? [Excel Homework]

Today we have home work for you. You need to help Johnny figure out how many hours he worked on splitting atoms. He has crazy boss who is behind him for that timesheet. Now Johnny needs your help to write the correct formula so that he can tell his boss how many hours he worked and go home.

Go ahead and read rest of this post and tell your answers using comments. Go!

Continue »