All articles in 'Learn Excel' Category
The other day, I found myself making copies of a templated report worksheet. After trying the usual route of “right click on source sheet, select move or copy, check create a copy and press OK” a few times, I thought “well that is asinine.” So I figured, may be CTRL+Drag will create a copy. And what do you know, it does.
So that is our quick tip for the day. Whenever you need to make a copy of something, simply hold CTRL key and drag the thing.
It works for charts, drawing shapes, worksheets and even ranges.Continue »
In this amazing guest post, the winner of our 2016 dashboard contest – Chandeep – Explains how he constructed the jaw dropping beauty (shown above) using Excel, creativity, love and sweat. Grab a full cup of coffee (or whatever liquid fancies you) and read on. Take lots of notes and play with the ideas in Excel while reading to maximize your learning.
Thanks Chandeep.Continue »
We recently went on a road trip around parts of New Zealand’s north island. We have been to Taupo, Rotorua, East Cape and Napier. It took us 2 weeks, we drove more than 2,000 km and spent almost NZ $3,000 on the trip. Of course, being a data nerd, I made a plan of the trip in Excel and that helped us budget for this.
After getting back to home, I thought it would be fun to polish the planner workbook and share it with you all so you too can plan a fabulous road trip. So here we go.Continue »
We are on a tiki tour around NZ. So far we have been to Taupo & Rotorua. And we are doing what you do when you are on a holiday – being lazy, going on walks, swimming in lakes, eating copious amounts of food and getting lost. Of course, all this means, I have very little time to access to internet & my blog. So the updates will be slow for next two weeks. Here is a quick tip (well, two of them) to keep you busy and awesome.
How to remove ugly formatting from your workbooks?
Do you have a colleague or boss (shudder) that loves to apply their special touches to every workbook their mouse lands on? Do you constantly wince and whine when you have to work on that spreadsheet.
Here are two handy ways to restore your data to its original glory.
Simple, select the data you want formatting gone from, go to Home > Clear > Formats.
And Excel will weave an expelliformat spell at your data and make it clean.
Here is a quick demo.Continue »
Let’s talk about the untrimmable spaces.
We all know that TRIM() removes extra spaces from the beginning, ending and middle of a text.
So for example, if A1 has ” something and one more ”
will give “something and one more”
We can use CLEAN() function to remove non-printable characters (like the ASCII codes 0 to 31). Of course, SPACE is technically a printable character, so CLEAN() won’t remove spaces.
The untrimmable spaces…?
The other day Sreekanth emailed me a sample of data and asked, “how do I remove the spaces in this list and convert them to numbers?”
Naturally I tried to TRIM().
But the data won’t budge. See above.
Hmm, let’s investigate why.Continue »
Let’s say you have some employee data in employee name, manager name format. But the data is all in one column, with odd rows containing employee names & even rows containing manager names. Something like above.
And you want to find out who is the boss for a given employee. Say, “Andrea Nichols”.
Your regular MATCH() formula for Andrea over the data range returns wrong answer as it will find first occurrence of Andrea (which in this case happens to be on even row, hence a manager record).
So how would you write the lookup formula?Continue »
First a quick personal update: There has been a magnitude 7.8 earth quake in NZ on 14th November 2016 early morning. It is centered in Kaikoura, which is about 250 km away from Wellington. We did feel several shakes and after shocks. It has been an interesting and often scary experience. But my family is safe. I feel very sad for the all the damage and the loss for families in NZ. If you suffered from this quake, My prayers and thoughts are with you.
Yesterday, a friend asked me an interesting question. He has school distance data, like above. He wants to know which is the closest school for each school.
There are a few ways to answer this question. Let’s examine two approaches – formulas & pivot tables and see the merits of both.Continue »
Over on Facebook, Kristin asks, Help, my blood pressure is going thru the roof. I can’t seem to solve this blood pressure problem.
Let’s simplify Kristin’s problem.
You have some data in the format shown above.
And you want to find out the BP category for each reading, using some rules. Read on to solve the problem.Continue »
Over at twitter, @for_the_moves asks,
@For_the_moves Same as growing your vocabulary. Remember, words (or functions) = ideas. the more you know, the better you can think.
— Chandoo.org (@r1c1) October 12, 2016
That got me thinking. How many functions should you care to learn?Continue »
Imagine you work at MI5 as a HR officer. You want to find all agents who have license to kill (licence 7). Your data looks like above.
How would you go about it?
If you filter the list or use FIND() or SEARCH() formulas, you will end up with agents who also have licenses 77, 17 or not7. So how would you solve this problem?
Of course, you do what any smart person does. You summon Excel and ask it nicely by using some wicked pattern matching logic.Continue »
Finally, spring weather showed up in Wellington this week. We cashed it as much as possible by going on treks, cycling trips, more treks and of course doing laundry.
Anyways, I don’t have time to blog. I must go out and help kids with some cycling. But I want to keep you busy this weekend. So here is a fun homework problem.
Does my range have all numbers from 1 to n?
Let’s say you have a range called range (duh!). And you want to check if range has all the numbers 1 to n (say n=5) in it, each number appearing only once (no more, no less). You can assume the named ranges range and n in your formulas.
See above examples to understand the problem.
So go ahead and post your formulas in the comments section. I will sneak in whenever I can to look at all your creative answers.Continue »
Hi friends & readers of Chandoo.org,
I am very happy to invite you to our newest online class, 50 ways to analyze your data. This program makes you an awesome analyst, training you on vital skills like data analysis, data science, visualization, modeling business problems and finding best solutions.
Please click here to know more about this program & enroll.
What is this course?
It is the age of big data. Alas, what we need is big insights. But finding even small insights buried in our data is a hard task. To find the stories hidden in your data, you need to follow a process like this:
- Collect & clean data
- Structure the data
- Model business problems
- Analyze the data (or solve the problem)
- Visualize results
- Find conclusions
- Add layers of complexity to the problem
- Build what-if scenarios
- Reach conclusions
- Take action
This is where the 50 ways to analyze your data course helps. In this program, we analyze 50 familiar, important and diverse business situations using several of the above steps.Continue »
Every week, we read news about failed analysis projects. If you listen carefully, you can hear the grunts, screams and curses of thousands of analysts all over the world about their analysis nightmares.
At Chandoo.org, we talk a lot about best practices for data analytics. So today, let’s peek in to the dark side and understand the mistakes that can turn your analysis project into a nightmare.
There are 3 parts in any analysis project
To understand these worst practices in analysis world, first let’s break analysis projects in to 3 parts.
- Data Structure
- Tools & Construction
Let’s deep dive in to each area of the analysis projects to see what can go wrong.Continue »
So here is a news from strange but true department. Microsoft Excel blamed for gene study errors [bbc.com].
Microsoft’s Excel has been blamed for errors in academic papers on genomics.
Researchers trying to raise awareness of the issue claim that the spreadsheet software automatically converts the names of certain genes into dates.
Gene symbols like SEPT2 (Septin 2) were found to be altered to “September 2”.
This is what happens when you spend countless hours learning genome sequencing and very little about the software tools where your data goes. May be we need clippy back to warn people about such sticky situations.Continue »
Over the weekend, I got an email from Mr. E, one of my students. Mr. E works at a police department in California and as part of his work, he was looking at calls received by police. Whenever police get a call for help, multiple teams can respond to the call and go to the location. All of these dispatches are recorded. So a single call can have several such dispatches. And Mr. E wanted to findout which team responded the first. The problem?
Finding the first responded team is tricky.
Today let’s take up this problem as a case study and understand various methods to solve it. We are going to learn about writing better lookups, pivot tables, power pivot and optimization. Put on your helmets, cause this is going to be mind blowingly awesome.Continue »