CP020: Top 10 time saving strategies for business analysts

Backlinkseller

CP020: Top 10 time saving strategies for business analysts
Zum Original Blog Chandoo.org

In the 20th session of Chandoo.org podcast, lets save some time.

CP020: Top 10 time saving strategies for business analysts - Chandoo.org Podcast

What is in this session?

We all want to save time and stay productive. The obvious answer seems like using keyboard shortcuts. But they can only get you so far. So what about the real productive strategies? That is what we address in this podcast.

In this podcast, you will learn,

  • Announcements
  • 5 key areas of business analyst work – tracking, analysis, reporting, data management & modeling
  • Time saving strategies for tracking
  • for analysis
  • for reporting
  • for data management
  • for modeling
  • Conclusions

Here is a mind-map of this podcast.

CP020 - Top 10 time saving strategies for business analysts - mindmap

Listen to this session

 

Links & Resources mentioned in this session:

  1. Tables: Use them to structure data & track easily

Thank you, Houston meetup & Bonus tip


Thank you, Houston meetup & Bonus tip
Zum Original Blog Chandoo.org

My mom will be very unhappy with this post. She always told me to focus on one thing at a time. But in this post we are talking about 3 things, not one. Sorry mom.

1. Thank you

I want to thank you for visiting chandoo.org & supporting us.

As I am about to leave to USA for attending Excelapalooza conference, I couldn’t help but be amazed at how much you have given me & my family. Almost 4.5 years ago, when I left my plush corporate job to work full time on Chandoo.org, I had no clue how the future will unfold. Today my heart is full of happiness, my family is secure, my site has grown by heaps and our community (especially you) is awesome.

Please help me design our new course – “50 ways to analyze your data”

Backlinkseller

Please help me design our new course – “50 ways to analyze your data”
Zum Original Blog Chandoo.org

Hi friends & readers of Chandoo.org,

I have an exciting and awesome new training program for you. 50 ways to analyze your data is aimed to give you critical data analysis & charting skills necessary for your success as an analyst (or manager). I want to make sure that this course offers you best content & value. I need your help in designing it. Please take a few minutes to read this short post & share your feedback.

introducing 50 ways to analyze your data course from Chandoo.org - Please help me design it

Here is what I have in mind:

As the name suggests, this course explains 50 different ways to analyze & visualize data. There are a million ways to skin our data cats. But at the end of the day, to succeed as an analyst or manager, we need to master just a handful of analytical techniques & concepts.

3D Max Formula for Excel


3D Max Formula for Excel
Zum Original Blog Chandoo.org

We all know about the MAX formula. But do you know about 3D Max?

Sounds intriguing? Read on.

Lets say you are the sales analyst at ACME Inc. Your job involves drinking copious amounts of coffee, creating awesome reports & helping ACME Inc. beat competition.

For one of the reports, you need to find out the maximum transactions by any customer across months.

But there is a twist in the story.

Your data is not in one sheet. It is in multiple sheets, one per month.

Like this:

3d max formula - what is the maximum of all months?

How to get the max value for all months?

Using 3D MAX formula

We can use 3D formulas in such cases. 3D formula?!?

Lets say our transaction data is in column C, in range C5:C44 in all sheets (same cells in all sheets)

Replace formulas with values with a simple wiggle

Backlinkseller

Replace formulas with values with a simple wiggle
Zum Original Blog Chandoo.org

Here is a very simple & fun way to replace formulas with values.

(Earlier, we talked about how to do this with keyboard)

Just wiggle.

Wiggle what?

Replace formulas with values using a simple wiggle trickFollow these steps

  1. Select all the cells that contain formulas
  2. Right click on the selection border (your mouse pointer changes to 4 sided arrow)
  3. Right click + Drag the selection a few cells away and drop them back at original place (ie wiggle)
  4. A menu appears. Select Copy here as values only.
  5. You are done

See the demo aside.

More fun & productive ways to use mouse in Excel

You can do a lot of powerful & useful things in Excel with mouse. Check out our Mouse shortcuts section.

Teach me (and rest of our community) something this Teachers’ Day

Teach me (and rest of our community) something this Teachers’ Day
Zum Original Blog Chandoo.org

Lets celebrate teachers day - share your tips with us

Today is teachers’ day in India. I owe everything I learned to my teachers. Their powerful, insightful & inspirational lectures make me a better person.

Today I want to recruit more teachers. Because you can never learn enough.

So why don’t you come on board and teach me (and rest of our website visitors) something?

All you have to do is post a comment with an Excel tip, technique, idea or experience with us. It can be related to anything, from formulas to solver, charts to power pivot, VBA to keyboard shortcuts. Anything goes.

Lets go. I am all ears.

Click here to teach.

Zum Original Blog
Autor: Chandoo

CP019: 6 Tips for Best Practice Modeling – Interview with Danielle from Plum Solutions

CP019: 6 Tips for Best Practice Modeling – Interview with Danielle from Plum Solutions
Zum Original Blog Chandoo.org

In the 19th session of Chandoo.org podcast, lets talk about modeling best practices.

CP019 - 6 tips for best practice modeling - Interview with Danielle from Plum Solutions - Chandoo.org Podcast - Become Awesome in Excel

What is in this session?

I am very happy to interview my good friend, blogger, author, excel trainer & business-women – Danielle Stein Fairhurst for this session. I first met Danielle when I went to Sydney, Australia in April 2012. Our friendship & collaboration grew a lot in the last 2.5 years. She is a great speaker & trainer. This episode is loaded with her trademark style commentary, explanation & tips for better modeling. I hope you will enjoy it.

In this podcast, you will learn,

  • Introduction to Danielle & her work
  • 6 Tips for Best Practice Modeling
    • Write consistent formulas
    • Avoid hard-coding
    • Smart referencing
    • Ditch the bad habits

Quickly convert numbers stored as text [tip]

Quickly convert numbers stored as text [tip]
Zum Original Blog Chandoo.org

Here is a quick tip to start the week.

Often, we end up with a situation where a bunch of numbers are stored as text.

In such cases, Excel displays a warning indicator at the top-left corner of the cell. If you click on warning symbol next to the cell, Excel shows a menu offering choices to treat the error.

Converting numbers stored as text back to numbers - using Convert to number option from cell error

Converting numbers stored as text back to numbers

Tip #1: Error correction

One easy and quick way to convert all the text-numbers to numbers is,

  1. Select all the cells
  2. Click on warning indicator next to top-most cell
  3. Choose Convert to number option
  4. Done

Tip#2: Paste Special Convert

Sometimes such text-numbers may be scattered across the worksheet, thus making selection of cells a pain.

CP018: Don’t be a Pivot Table Virgin!

CP018: Don’t be a Pivot Table Virgin!
Zum Original Blog Chandoo.org

In the 18th session of Chandoo.org podcast, lets loose your Pivot table virginity.

Note: This is a short format episode. Less time to listen, but just as much awesome.

CP018: Don't be a Pivot Table Virgin! - Introduction to Excel Pivot Tables - Chandoo.org Podcast

First some good news!

Our podcast crossed 100k downloads. Yay!!!

That is right. It took us 6 months & 17 episodes to reach this milestone.

Thanks for loving Chandoo.org podcast. You are awesome.

PS: If you have not already subscribed, get it here from iTunes (other ways to subscribe).

What is in this session?

Pivot tables are a very powerful & quick way to analyze data and get reports from Excel. But surprisingly, not many use them. Today, lets bust your pivot table virginity and understand the concepts like pivoting, values, labels, filters, groups and more.

In this podcast, you will learn,

Convert fractional Excel time to hours & minutes [Quick tip]

Convert fractional Excel time to hours & minutes [Quick tip]
Zum Original Blog Chandoo.org

Time for another quick Excel tip.

Convert fraction to hours & minutes in Excel - Tip

Lets say the park near your house rents tennis courts by hour. And they charge $10 per hour. At the end of an intense tennis playing week, Linda, the tennis court manager called you up and said you need to pay $78 as rent for that week.

How many hours did you play?

Of course 78/10 = 7.8 hours.

But we all know that 7.8 hours makes no sense.

We also know that 7.8 hours is really 7 hours 48 minutes.

So how to convert 7.8 hrs to 7:48 ?

That is our quick tip for the day.

Custom cell formatting to show hours & minutes in Excel from fractional timeSimple, assuming the fractional time is in cell A1,

Use below steps to convert it to hours & minutes: