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:
Select any cell in your data
Press CTRL + T or click on Insert > Table
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.
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 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.
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.
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.
Select first list.
Hold CTRL key and select the second list. This highlights both lists.
Go to Home > Conditional Formatting > Highlight cell rules > Duplicate values
Viola, you can instantly see which values are common in both lists.
Bonus tip: If you want to see which values are unique to each list, just flip the highlight rule from dialog.
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.
Highlight walk-ins column
Go to Home > Conditional formatting > Top/bottom rules > Top 10 items..
Click ok (or change the number if you fancy)
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.
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:
Select walk-ins column
Go to Conditional Formatting > Highlight cell rules > Between…
Either type in the range or point to cells containing values.
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.
Simply apply filters on your data (press CTRL+Shift+L to turn on filters)
Click on the timestamp / data / time header and enter the time or date you want to search for.
Type March to see all March entries
7AM to see all entries with 7AM as hour part
2017 to see all entries logged in year 2017
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:
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).
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.
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,
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.
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:
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,
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
Read for later
Articles marked as Favorite are saved for later viewing.
Scroll to Top
Separate tags by commas
To access this feature, please upgrade your account.