Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

All articles with 'spreadcheats' Tag

31 Excel Tutorials – Learn and Be Awesome in Excel

Published on Mar 11, 2010 in Charts and Graphs, Featured, Learn Excel
31 Excel Tutorials – Learn and Be Awesome in Excel

In October 2008, I have started an ambitious series of posts on this blog called – Spreadcheats. These are little tricks, nuggets, tutorials on using Excel that would make anyone a spreadsheet guru.

The spreadcheats series has been wildly successful. I am compiling all this useful information and articles in to one big post so that anyone can follow the links and become good in Excel. Read on,

[Note: This is not for beginners. If you know what a formula is, you would enjoy this 31 articles]

Continue »

Collapse, Expand Excel Charts using this hidden trick

Published on Jan 12, 2010 in Charts and Graphs
Collapse, Expand Excel Charts using this hidden trick

Do you know that you collapse or expand excel charts? Don’t believe me? Me neither. When I first realized that we can collapse / expand charts without writing any macros or lengthy formulas, I couldn’t wait to share it with all of you. This is such a simple yet powerful trick. See it for yourself.

The trick lies in using group / un-group data feature and carefully aligning charts with the cell grid. The whole process takes just about 2 minutes and produces wow factor worth a week. Go check it, now. Also, a downloadable template is included in the post.

Continue »

Print Worksheets in One Page Across [Quick Tips]

Published on Dec 22, 2009 in Excel Howtos
Print Worksheets in One Page Across [Quick Tips]

Have you ever wondered how to print excel sheets in one page across? so that when you print a large excel spreadsheet the output can be read one page after another. We can use the print settings in Excel to do this very easily. In this episode of quick tips we will learn how to do this with in 5 seconds.

Continue »

What is Excel SUMPRODUCT formula and how to use it?

Published on Nov 10, 2009 in Featured, Learn Excel
What is Excel SUMPRODUCT formula and how to use it?

Today we will learn a new and exciting excel formula – the all powerful SUMPRODUCT.

At the outset SUMPRODUCT formula may not seem like all that useful. But once you understand how excel works with lists (or arrays) of data, the SUMPRODUCT’s relevance becomes crystal clear.

Continue »

Prevent Duplicate Data Entry using Cell Validations

Published on Oct 26, 2009 in Excel Howtos
Prevent Duplicate Data Entry using Cell Validations

We all know that data validation is a very useful feature in Excel. You can use data validation to create a drop-down list in a cell and limit the values user can enter. But, do you know that you can use data validation in a multitude of ways to prevent users from entering wrong data?

Here is a practical application: Prevent users from entering duplicate values in a range of cells.

For eg. you are making an invoice. Wouldn’t it be cool if Excel prompted you when you enter a duplicate line item so that increase the item quantity instead of repeating it.

Continue »

Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one

Published on Aug 19, 2009 in Excel Howtos, Featured, Learn Excel, Pivot Tables & Charts
Excel Pivot Tables Tutorial : What is a Pivot Table and How to Make one

Excel pivot tables are very useful and powerful feature of MS Excel. They can be used to summarize, analyze, explore and present your data. In plain English, it means, you can take the sales data with columns like salesman, region and product-wise revenues and use pivot tables to quickly find out how products are performing in each region.

In this tutorial, we will learn what is a pivot table and how to make a pivot table using excel.

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 »

Build a Retirement Calculator using Excel (learn how to use Goal Seek as a bonus)

Published on Jul 29, 2009 in Excel Howtos, Featured, Learn Excel, personal finance
Build a Retirement Calculator using Excel (learn how to use Goal Seek as a bonus)

In this installment of spreadcheats, we will learn how to use goal seek feature of excel. We will build a retirement savings calculator using excel. We will learn to use Excel’s FV() formula to estimate the corpus that can be accumulated by saving fixed amount every month.

Continue »

Do you know these Double-click Tricks in Excel?

Published on Jun 12, 2009 in Excel Howtos, Featured, Learn Excel
Do you know these Double-click Tricks in Excel?

Most of us think of mastering formulas, learning macros and being supergood with charts when we think of being productive with spreadsheets. But often learning simple stuff like keyboard shortcuts, using mouse and working with menus and ribbons can be a huge productivity booster for us. So as part of this installment of spreadcheats we […]

Continue »

Statistical Distributions in Excel [spreadcheats]

Published on Jun 1, 2009 in Learn Excel
Statistical Distributions in Excel [spreadcheats]

Excel has very powerful formulas and add-ins for performing almost any kind of statistical analysis. Today we will learn how you can make a statistical distribution of test scores using excel. There is a downloadable workbook too, for you to take a look at the formulas and play with them.

Continue »

Create a Dynamic Chart in Excel in 2 Minutes [spreadcheats]

Published on May 19, 2009 in Charts and Graphs, Featured, Learn Excel
Create a Dynamic Chart in Excel in 2 Minutes [spreadcheats]

Here is a really fun and simple way to create dynamic charts in Excel. It uses data filters in an innovative way and creates the dynamic effect. To create the dynamic charts, just follow these 5 steps:

(1) Prepare your charts: Make as many charts as you want. Lets say 3.
(2) Set up the area where dynamic charts will be loaded: Just take 3 cells in a row and adjust the row height and column width such that the charts can be fit inside snugly. Also, type the chart names (1 for each cell) in the cell. Let us say, the charts you have are for Costs, Sales and Profits, just type these names in the cells.
Read more…

Continue »

Calculate Moving Average

Published on Apr 28, 2009 in Learn Excel
Calculate Moving Average

As part of our spreadcheats, today we will learn how to calculate moving average using excel formulas. As a bonus, you will also learn how to calculate moving average of latest 3 months alone using the count, average and offset formulas. Once you finish the tutorial, you have some home work to do as well. So go ahead and learn how to calculate moving averages.

Continue »

Excel Formula Errors – Understand and Debug Them

Published on Apr 20, 2009 in All Time Hits, Excel Howtos, Featured, Learn Excel
Excel Formula Errors – Understand and Debug Them

In this installment of spreadcheats we will learn how to understand excel formula errors and fix them. The #DIV/0!, #NAME?, #N/A, #NULL!, #NUM!, #REF!, #VALUE! errors and how to fix them.

Continue »

Using Array Formulas in Excel – Find if a list has duplicate items

Published on Mar 25, 2009 in Excel Howtos, Learn Excel
Using Array Formulas in Excel – Find if a list has duplicate items

In this installment of our spreadcheats series, we will learn how to use array formulas and findout if a list has any duplicate items. We will use Countif and array formulas to do this …

Continue »

Excel Conditional Formatting Basics

Published on Mar 13, 2009 in Excel Howtos, Learn Excel
Excel Conditional Formatting Basics

Do you know What is excel conditional formatting? Learn the basics, few examples and see how you can use it in day to day work in this installment of spreadcheats.

Continue »