Loading...

Follow Chandoo.org - Learn Excel & Charting Online on Feedspot

Continue with Google
Continue with Facebook
Or

Valid


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

So how to use tables?

Simple, select any cell in your data and press the big button. 

To use a table:

  1. Select any cell in your data
  2. Press CTRL + T or click on Insert > Table
  3. Bingo, your data is now a table.

Excel will name this table as Table3 or something like that. Just use Design tab to rename the table to whatever you want.

But why use tables at all?

Tables offer many powerful data analysis, reporting and storage features.

  • Structure and format the data: As soon as you turn your data in to a table, Excel will apply several quick formatting rules to your data. This simplifies how you enter, read and understand your data. Also, tables are logical structures to store your data. So when you add data at the end of table, the formatting and range for the table automatically extends.
  • Use structural references: If your data is in tabular format, you can apply structural referencing to write formulas. This is so much simpler than cell address based referencing of data. For example, to count number of staff at Manager level, you can use this formula:
    =COUNTIFS(staff[Manager?],”Manager”)Tutorial: All you need to know about structural references and table formulas.
  • Apply sub-totals and filters with ease: You can add a total row to any table to show various kinds of totals (sum, count, average etc.) for the displayed data. If you filter, update or add to your table, the totals change too.
  • Slice your data: Starting with Excel 2013, you can apply slicers to Excel tables. This way you can visually filter your data with ease.Tutorial: All about Excel slicers
  • Connect multiple tables with relationships: Starting Excel 2013, you can connect multiple tables just like a database. Once you have a data model like this in Excel, you can create powerful and insightful connected pivot tables too. Learn all about table relationship feature.
  • Send tabular data to Power Pivot, Power Query or Power BI with ease: Tabular data can be easily accessed by power tools like Power Pivot for Excel, Power Query (Get & Transform Data) and Power BI. This liberates your Excel data and enables powerful data clean up, analysis and visualizations.
  • Analyze data without hassle: If you create a pivot table from tabular data, then any changes to table are available to pivots automatically. You just need to refresh the pivot reports to see updated summaries. Learn more about Excel pivot tables.
  • Visualize data with ease: If you create a chart from tabular data, then anytime your table is updated (add new rows, delete rows or update data), then your chart is updated automatically. This enables powerful interactive and dynamic charting experiences for your users. Check out below dynamic chart examples.
Become a table genius then…

Press your big button without hesitation (or simply use CTRL+T) to enter the awesome world of tables. Check out below tutorials and tips to guide you along the way.

Are you a #TableGenius?

Are you #XLTableGenius? Go ahead and thump your chest and let the world know. Tweet with #XLTableGenius and post comments about how you rock the world with tables.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Here is a New year gift to all our readers – free 2018 Excel Calendar & daily planner Template.

This calendar 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.
Download 2018 Calendar – Excel File

Click below links to download the calendar you want:

How does this Calendar work?

This uses same techniques as mentioned in previous calendars. So check out this page to learn.

Go ahead and enjoy the download. The file is unlocked. So poke around the formulas and named ranges. Learn some Excel.

More Calendar Downloads:

Download these additional calendar templates and start your new year in awesome fashion!

Techniques used in the calendar template: INDEX OFFSETINDIRECT | Array Formulas | Using Date & Time in Excel | Conditional Formatting

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Hellow there…

I wish you a merry Christmas & Happy New Year 2018. May your holidays be filled with joy, togetherness, celebrations and fulfillment. May your new year be filled with hope, energy and awesomeness.

I want to tell you how thankful I am for all your support in this year. Each time you click, visit, read, comment, share, view, listen, purchase, subscribe, follow, question, discuss or email, my heart is filled with gratitude and happiness. Thank you for all the love and support. 2017 has been another amazing year in Chandoo.org journey and I owe this to you.

About this year’s holiday card

This is taken at Castlepoint light house, near west coast of Wairarapa in New Zealand. It was a splendid day to hike, enjoy by the beach and watch some hot air balloons float by. You are looking at me, Jo (wife), Nakshatra (daughter) and Nishanth (son).

Holiday schedule of Chandoo.org

Right now, we are all in India, enjoying first vacation since moving to NZ. So, I won’t be logging in to post anything until first week of January. Enjoy your holidays and I will say hello to you in January 2018.

Our forum:

Our Excel forum will be up and running during the holiday season. That said, I would not expect quick help for any problems as most of our regular members would be away on holiday break. You have higher chance of getting rand() = rand() than finding someone answering your Excel questions immediately.

Online store:

Our online store will be open. Any purchase you make for training programs, templates or eBooks will be delivered as promised. If you join our course on Christmas eve or day or on new year day, please expect to receive your password by the end of next working day. If you have any support query, please expect to hear a resolution by 8th of January.

Emails:

If you are sending me an email, please expect a reply only after 9th of Jan. If you have something urgent to say, you can call me (find my number on the site).

Once again…,

On behalf of my family, our staff, volunteers and well wishers, let me wish you a very pleasant holiday season and an awesome start to 2018.

PS: Here is some holiday fun – Christmas card & snow flakes  made in Excel.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Hello folks,

Our holiday sale for 2017 is now live. Click here to purchase your favorite Excel, advanced Excel, dashboards & VBA courses at discount.

What is this holiday sale?

Two of our most popular, awesome & valuable courses are on sale.

  1. Excel School & Dashboards program – Save $30
  2. Excel School, VBA & Dashboards program – Save $50
What do you get in these courses?

In Excel School & Dashboards program, you will get

  • Detailed & Step-by-step training on all day to day aspects of Excel
  • Tutorials & examples to make you advanced user in Excel
  • Knowledge on how to use various important formulas, how to combine them
  • How to set up tables, analyze data with pivot tables & do so much more
  • How to create charts, how to add interactive, dynamic features to them
  • Instructions on how to create world-class dashboards, MIS reports

Excel School program has more than 32 hours of video training, 50+ example workbooks & files, 1 year unlimited access so that you can become awesome at a pace that works for you.

In Excel School, VBA & Dashboards program,

  • Everything in Excel School & Dashboards as mentioned above
  • Detailed & Step-by-step training on VB language & macros
  • Thorough understanding of VBA Eco system
  • Dozens of examples on loops, conditions & common coding techniques
  • Overview of SQL, Access & File systems
  • Elaborate examples on user forms, MS Access integration
  • Practical track with every day work problems & solving them with VBA
  • Interesting class projects, homework to test your skills

VBA Classes includes more than 50 hours of video training, 100+ example files, macros & workbooks, 1 year unlimited access so that you can become a rock star in Excel, VBA & Dashboards.

When is the SALE closing?

This holiday sale closes Friday mid-night – that is 15th December, 2017 mid night – Pacific Time.

Hurry up and Enjoy the sale

Go ahead and enroll in one of these courses. Become awesome in Excel.

Click here for the holiday sale page.

PS: This year alone, more than 1,000 people have enrolled in above 2 courses. You too can unlock the power of Excel & make difference in your work. Go ahead and join us.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

‘Tis Friday and it is too hot in my home office to stand and type a longish post. So, let’s keep this skirtish (short and pretty).

How would you calculate long service bonus?

Let’s say you are HR manager at BigLargeInc. and you are looking at Julia’s service details. You have her employment start date, current date, her leave without pay details, as shown below.

You need to calculate how many days of continuous service Julia has (ie total service – duration on leave without pay). How would you write the formula?

Assume cell references as shown in the picture above.

Please post your answers in comments.

Bonus question? Think Julia’s troubles are nothing? Then come up with formulas to tell how many years and months of long service Julia had. Assume each year has 365.25 days and 12 months in it.

Answers for the example above:

For the above data, you should get below answers

  • Continuous service days: 4,870
  • Continuous service in years and months: 13 years and 4 months

Go ahead and post your formulas in comments. Julia is waiting for her bonus.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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.

1. Highlight matching / missing items in two lists

Everyday millions of people ask – “Which items are common in these two lists?” and then most of them waste several minutes (or hours) comparing the lists. But you can answer the question in just five seconds. It is so simple and elegant.

  1. Select first list.
  2. Hold CTRL key and select the second list. This highlights both lists.
  3. Go to Home > Conditional Formatting > Highlight cell rules > Duplicate values
  4. Viola, you can instantly see which values are common in both lists.
  5. Bonus tip: If you want to see which values are unique to each list, just flip the highlight rule from dialog.

Related: Compare two lists in Excel [complete guide] | Compare things in Excel – podcast

2. Highlight top 10 items

Once again, a common problem faced by lots of people everyday. Which items are top / bottom n in this list?

The answer is simple. Just select your list and apply top / bottom rules.

Let’s say you have monthly customer walk-ins at your store as a list, like below.

You want to know which are top 10 days in November for customer walk-ins.

  1. Highlight walk-ins column
  2. Go to Home > Conditional formatting > Top/bottom rules > Top 10 items..
  3. Click ok (or change the number if you fancy)
  4. Done and done.

Pro tip: The default top / bottom rules only highlight the value column. If you want to highlight entire row or the corresponding date (or other data), you can use a formula based rule, like below:

Say your data is in A1:B30 and you want to highlight the rows where value in column B is top 10.

Select your data (A1:B30), go to Conditional formatting > New Rule. Select “Use formula…” option. Type in =$B1 >= LARGE($B1:$B30,10) and set up formatting. Click ok and top 10 items in your data will be highlighted.

3. Visualize changes over time with elegant icons

Things change, people change, money changes and most importantly, data changes… all the time. So how do you quickly and elegantly visualize how things have changed over time? Simple, apply conditional formatting icons to spot the changes.

Let’s go back to our store walk-ins example from #2.  We want to see the trend like this:

To get this, in the adjacent column, write this simple formula to compare walks-ins with previous day.

Now, select “Trend” column and go to Conditional formatting > New rule

Select format style as “Icon sets” and apply the rule as shown below.

Bingo, your cute trend icons are ready.

Related pro tip: Don’t just show simple numbers in your reports and dashboards.

4. Top customers by category

Time to ramp up the game. Let’s say you run a sporting goods store and you are looking the category-wise units sold to each customer, like below.

Your question: Which customers are top in each category?

Unfortunately, we can’t use default top / bottom rules to answer this question. But we can use a tidy little formula to get the answer. Let’s say our data is in the range $R$6:$T$124.

  1. Select your data, go to Conditional Formatting > New Rule
  2. Select “Use a formula…” type of rule
  3. Write the rule =$T6 = MAX(IF($R$6:$R$124 = $R6, $T$6:$T$124))
  4. Set up formatting as you want
  5. Done.

Check out below illustration to understand how this rule works:

And the result is awesome:

Related: MAXIF formula explained

5. Highlight values in a range

Often we want to narrow our focus to a small range so we can analyze better. Let’s go back to the store walk-ins example. If you want to highlight all days when the walk-ins are between 145 to 160 (the sweet spot as your manager calls it), you can use the built-in between rule, like below:

  1. Select walk-ins column
  2. Go to Conditional Formatting > Highlight cell rules > Between…
  3. Either type in the range or point to cells containing values.
  4. Done.

Related: BETWEEN formula in Excel

Top 5 conditional formatting tips – Example workbook

Click here to download the workbook with all these tips and sample data. Play with it to learn more. Try to implement your own rules to understand CF better.

What are your top conditional formatting tips?

Over to you. What are your top conditional formatting tips? Please share them in the comments section.

More conditional formatting tips:

Conditional formatting is one of my favorite Excel features. I talk about it all the time. Check out below tutorials for more awesome tips.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Imagine you are the first officer at ship terminal αε974F1 on remote planet Alderaan. Your job involves looking at terminal log to see anomalies in time space continuum. So one day after getting to work late, thanks to crazy traffic on the floating super way in your settlement, you are looking at latest terminal log for αε974F1 on Excel (of course Excel, what else are you going to use? Notepad?!?) and want to check all the records logged at 7 AM on any day. You don’t have all the time in universe to filter records one at a time. You don’t want to write a formula or something else as it is too early in the morning and the nearest Starbucks is 7 light years away. So what would you do?

Use filters of course.

  1. Simply apply filters on your data (press CTRL+Shift+L to turn on filters)
  2. Click on the timestamp / data / time header and enter the time or date you want to search for.
  3. For example:
    1. Type March to see all March entries
    2. 7AM to see all entries with 7AM  as hour part
    3. 2017 to see all entries logged in year 2017
    4. Or use the arrow symbol at the end of search field to search by year / month / date etc.

Here is a quick demo of how date & time filtering works.

Wait a sec, what if I want to see all records for Monday?

Alas, as of star date 11712.04 (that is December 4th, 2017), Excel hasn’t evolved to filter by Mondays. To do this, you have to extract weekday portion to another column and filter by that. You can use either =WEEKDAY([@Timestamp]) or =TEXT([@Timestamp],”DDDD”) to extract weekday portion of the date in numbers (1 to 7) or text (Sunday to Saturday).

More filtering techniques

It looks like a slow day at terminal αε974F1. Why not catch up on some Excel awesomeness while you wait for that hot brewed coffee from nearest starbucks to be teleported. Check out:

My job is here is done. Beam me up Scotty.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Time for a quick residential break from all the Excel awesomeness. I have an exciting news to share with you all.

We have a home in New Zealand.

New Zealand? I thought you are in India!

In the middle of 2016, we moved to New Zealand. We have been living in windy, lovely and beautiful Wellington ever since. After renting a house and sampling life in NZ, we decided to call it home for next few years. The next natural step is to buy a home and move in. Unfortunately, this is where things went crazy for a few reasons.

Buying a house in Wellington
  • The housing market in New Zealand (more so in Wellington) was going gangbusters with prices increasing every week.
  • There is very limited stock and any modern, warm, well situated house would go in to a bidding war and sell for ridiculous amounts over asking price.
  • I never liked the idea of paying a mortgage. I have bought all my houses (except the first one) with cash. But that was in India where prices are amazingly cheap compared to markets like New Zealand. After shuffling around some funds from India and living well below means for the last 18 months, we had enough to pay good chunk of purchase price. We still needed some mortgage, but it is not terrifyingly large and I could pay it off if needed.

So after going to numerous open homes, reading heaps of builders reports and losing half a dozen bids, we were ready to give up. Then almost randomly, we ended up purchasing our home.

Details and pics of new Chandoo.org HQ

This is by far the biggest house we have ever owned or lived.

  • Size: 170 sqm house, 625 sqm land
  • 4 bedrooms (3 rooms where we will sleep and other will be the real Chandoo.org HQ, my home office) and open plan living with dining and kitchen
  • Location: northern suburbs, Wellington, NZ

Check out the pictures.

All the furniture is seller’s. We are moving in this Friday (24th of November).

From outside:

Living room:

Kitchen:

Master Bedroom:

View from back:

Thank you & Thanks to Excel

It is amazing how much of my life is owed to you. Thanks to your enthusiasm to learn, I get to live in a beautiful home in one of the best cities in world. I get to raise a family, travel, do awesome things and feel purposeful, all thanks to you.

It feels fitting that we are moving in to our new home on thanksgiving weekend.

Thank you.

I am also thankful to Microsoft, for creating one of the most wonderful software ever. Without Excel, you wouldn’t be reading this blog, I wouldn’t be living in New Zealand. Thank you MS.

Thank you also to my family, teachers, friends & colleagues for helping me learn, grow, make, fail, prosper and understand.

More Personal Stories

If you would like to learn more about the personal side of Chandoo.org, have a read thru these,

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

We had to switch power providers soon, so I started reviewing the options. There are heaps of providers in New Zealand and each offer a ton of different plans. Some offer welcome bonus or credit worth up to $ 200. Other offer straight forward rates. Some others offer discount if you sign up for both electricity and gas with them. So how do you decide which one is better for you?

Using Excel of course.

The result is awesome. I ended up saving more than $1000 with a simple model. Puzzled? Curious? Check out this short but powerful video tut.

Choosing Electricity Provider using Excel – Video

Check out the video tutorial explaining the problem and model building process. You can see it below or on our YouTube channel.

Download Example Workbook

Click here to download example workbook with all the calculations. Feed your own power plan details to see which one is better for you.

What everyday problems do you solve with Excel?

While I don’t build models to figure out if brewing coffee at home is better than buying it at local café (home brewing any day for me), I do use Excel quite a bit to answer everyday questions. Recently, I built a model to see how often I cycle vs. walk.

What about you? Do you answer everyday questions using Excel? Please share your experiences in the comments section.

Want more models? Tall, sexy, funny and flexible, we got them all:

If you want to learn how to build models using Excel, check out below tutorials & resources:

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Yesterday, my mate from across the ditch, Hui posted about conditional rank formula (RANKIFS) using awesome SUMPRODUCT.

Of course, not everyone can whip up a sumproduct formula like that. On a scale of One to Hui of Excel awesomeness, you would need to be at least an H to write sumproduct or countifs formulas shown in that post. So does it mean, you can’t conditional rank if you don’t know your X from L?

Don’t worry. We got you covered. You can still get your conditional ranks, without inception level array formulas. Simple, use pivot tables instead.

Two level (or multi-level ranking) with Pivot Tables

Let’s say you have data like this and you want to know what the department level rank of each student.

The process for creating these ranks is ridiculously simple. Just,

  1. Insert a pivot table from your data
  2. Add level and detail fields to row labels area (in this case, we shall add Department and Student to row labels area)
  3. Add field on which you want to rank to values area (ie Score)
  4. Right click on value field and go to “Show values as” > “Rank…”
  5. Set up base field as the lower level (ie Student in our case)
  6. Bingo, your ranking problem is sorted.

Here is a quick demo of how to use ranking feature in Pivot Tables.

Download Example Workbook:

Click here to download workbook with the few pivot table ranking scenarios. Check it out to learn more.

Do you use pivot tables for ranking?

I use them all the time for such complex, filter driven ranking in my reports. While we could use formulas to get similar results, I think Pivots shine when it comes to ranking, as they naturally resolve ties (without complex deduplication logic) and give results in short time. The only problem is we need to refresh them whenever there is a change in data. But it is a small price to pay for avoiding complex formulas.

What do you think?  Do you use pivot tables for ranking your data? Share your stories in the comments section.

Also, check out our Pivot Table Central for all things pivot, right from basics to ninja-level stuff.

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free year
Free Preview