All articles with 'Excel 101' Tag

Let’s start the new year with a bang.

Excel Tables were introduced more than a decade ago, but a lot of people don’t know them or under utilize them. So start this year by becoming a very table genius.

What is Excel Table?

Excel tables are a simple and elegant way to structure and store your data. Let’s say you have staff details like below. Instead of calling it like A1:E72, you can convert this data in to a table and call it, you guessed it right, covfefe (or more coherent option like – staff).

Time for another round of unconditional love. Today, let’s learn about conditional formatting top tips. It is one of the most useful and powerful features in Excel. With just a few clicks of conditional formatting you can add powerful insights to your data. Ready to learn the top tips? Read on.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 56th episode of Chandoo.org podcast, let me answer the chicken and egg question of Excel users. How many formulas should you care to learn?

What is in this session?
In this podcast,

• Two personal updates
• 3 legs of formula writing
  • Function knowledge
  • Operators
  • Referencing
• 6 categories of must-know functions
  • Basic math
  • Conditions
  • Lookups
  • Text
  • Date & time
  • Work specific
• Closing remarks & resources for you

At Chandoo.org, we are big believers of keyboard shortcuts. There are several posts (1,2,3,4, more and even more) discussing useful Excel shortcuts. Today I want to introduce a new kind of keyboard shortcuts. One hand shortcuts.

One hand shortcuts – Half the work, double the fun

The idea is simple. When you can use only one hand to complete the shortcut key presses, it is called as a one hand shortcut.

Let’s say you have a date in A1 and want to find out future date after 2 years, 4 months and 9 days.

Here are a few formulas you can try.

1. =A1 + DATE(2,4,9)
2. =EDATE(A1, 2*12+4) + 9
3. =A1 + 2*365 + 4*30 + 9

Surprisingly, each formula gives a different result! So which one should you use?

Ever wanted to show your workbook to someone and felt that you had less screen real estate? This tip will help you get more out of your workbook.

So how to get 50% more space for your workbooks?

Simple, just follow these steps.

Here is a simple IF formula challenge for you. Go ahead and post your answers in the comments section. Can this person vote in Sumeria? Imagine you are the chief election officer in the great country of Sumeria. You have introduced a new eligibility criteria for voters just before the grand presidential elections of 2016. In order […]

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

This is going to be epic!!! In the 50th session of Chandoo.org podcast, we have 50 Excel tips to make you awesome.

What is in this session?

In this podcast,

• Thank you message
• Fifty tips in 5 buckets
  • Shortcuts & Productivity
  • Formulas
  • Managing Data
  • Charts
  • Using Excel better

Let’s keep this simple & short.

Whenever you are editing cells or formulas, the usual sequence is like this:

1. Double click on the cell you want to edit
2. For existing cells: Go to the left most / right most part and start typing
3. For blank cells: start typing right away

Here is a faster sequence:

Read on…

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 43rd session of Chandoo.org podcast, let’s talk about top time saving features of Excel.

What is in this session?

In this podcast,

• Quick announcement about Awesome August
• My 9 favorite time saving features of Excel
• Remove Duplicates
• Tables
• Pivot Tables
• Auto fill
• Format Painter
• Find & Replace
• VBA / Macro Recorder
• Auto save
• Auto complete / Intellisence
• Recap & Conclusions

Here is a very simple but effective tip: use print areas. Oh.. but what is a print area? Imagine looking at a report or dashboard worksheet. Now, that sheet also has few calculations, some extra stuff to the right of the report. When you print, you don’t need those extra elements. Just the report will do. This […]

To make friends in a new town hit the bars – Old saying.
To make sense of a new data-set, make bar charts – New saying.

Bar charts (or column charts if you like your data straight up) are vital in data analysis. They are easy to make. But one problem. By default, a bar chart show the original data in reverse order.

See the above example.

Unfortunately, we humans read from top to bottom, not the other way around.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 41st session of Chandoo.org podcast, Let’s take a trip to data hell and meet 6 ugly, clumsy, confusing charts. I am revisiting a classic Chandoo.org article – 6 Charts you will see in hell.

What is in this session?

In this podcast,

• Quick announcement about Awesome August
• 6 charts you should avoid
• 3D charts
• Pie / donut charts with too many slices
• Too much data
• Over formatting
• Complex charts
• Charts that don’t tell a story
• Conclusions

A significant portion of our time in Excel is spent navigating between various ribbon tabs and clicking buttons. Wouldn’t it be better if you can combine all the frequently used features in to one ribbon? Something like above:

Read on to learn how to create, edit and share custom ribbons in Excel.

Excel has many powerful & time-saving features. Even by Excel’s standard, Flash Fill is magical. Introduced in 2013, Flash Fill is a rule engine to Excel’s fill logic. Every time you type something in a cell, Excel will try to guess the pattern and offers to fill up the rest of cells for you. That is some serious time saving magic.

Let’s understand what Flash Fill is and few sample use cases.