All articles with 'spreadcheats' Tag

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 …

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.

Learn how to use excel chart templates in this installment of spreadcheats. Save time.

We are a big advocate of keyboard shortcuts. I think learning a handful of keyboard shortcuts can improve your productivity tremendously, whether you are using excel or firefox.

But using only keyboard shortcuts is like using only right hand. So, we have compiled 5 incredible ways mouse can help you be productive while using MS Excel.

Learn how to create Table of Contents in Excel and other tricks to manage when you have too many worksheets.

As part of our spreadcheats series, we will learn how you can use data filters to create dynamic charts in Microsoft excel

Today we will learn how to make an employee satisfaction survey and consolidate the data using excel.

A simple label formatting hack can improve the effectiveness of YoY change charts by adding color to differentiate positive vs. negative growth (or mediocre vs. sky rocketing growth rates). See this example:

A combination chart is when you combine two different charts to make one. A popular example for combination chart is a line & bar graph combination.

Here is a very quick spreadcheat for the new year.

If you need to select all the cells in a series (either row-wise or or in columns) you can use one of these 2 shortcuts (and avoid unnecessary scrolling)

1. Select the first cell in the series

2. Press Shift + Ctrl + Down arrow

Or

1. Select the first cell in the series

2. Hold down SHIFT and Double click on the edge of cell in the direction you want to select

A very quick spreadcheat for Friday. If you need to type content in a cell and you want to see it multiple lines then use alt + enter to break the content in several lines. See to the right for an example.

Bonus tip: If you are using formulas to create content in a cell by combining various text values and you want to introduce line breaks at certain points … For eg. you are creating an address field by combining house number, street name, city and zip code and you want to introduce line breaks after house number and street name then you can use CHAR(10).

Can you imagine building a complex worksheet without formulas? I can’t. While no one can dispute the usability of formulas, we all know how painful it is when an excel formula returns a mysterious error and we don’t know what is causing the error.

Camera tool is your way of creating visual reference in an excel sheet. It is one of the useful and hidden features of excel. Here is how it works. You specify a rectangular area in your workbook and camera tool creates a mirror image of that area as a drawing object. You can move it or resize it. And whenever the contents of original rectangular area changes (charts, drawings or cell values) the mirror image changes too.

Data validation is a great way to keep your users informed about possible values in a cell and guide them to select something appropriate. As part of the spreadcheats series, in this post we will discuss 2 advanced data validation techniques that can help you when you are modeling a complex worksheet.

If for every countif() I write excel paid me a dollar, I would be a millionaire by now. It is such a versatile and fun formula to work with that I have decided to write about it as third post in our spreadcheats series. Using COUNTIF() to replace pivot tables: We all know that you […]