All articles with 'screencasts' Tag

Everyone likes to be in control. Even my 2 year old daughter jumps with joy when she lays her hands on TV remote. She pushes the buttons and assumes it is working. It is another story that we rarely watch TV at home.

By adding an element of control, we can make our dashboard reports fun. Interactive elements like form controls, slicers etc. invite users to play with your dashboard, get involved and understand data by asking questions. That is why I recommend making dashboards interactive.

Today lets understand how you can make dashboards interactive.

Whenever we deal with large amounts of data, one of the goals for analysis is, How is this data distributed?

This is where a Box plot can help. According to Wikipedia, a box plot is a convenient way of graphically depicting groups of numerical data through their five-number summaries: the smallest observation (sample minimum), lower quartile (Q1), median (Q2), upper quartile (Q3), and largest observation (sample maximum)

Today, let us learn how to create a box plot using MS Excel. You can also download the example workbook to play with static & interactive versions of box plots.

When looking at a big table of analysis (or data), it would make our life simpler if the selected cell’s column and row are highlighted, so that we can instantly compare and get a sense of things. Like above.

Who doesn’t like a little highlighting. So lets learn how to do highlighting today.

Jo (wife) likes to watch Masterchef Australia, a cooking reality show every night. Even though I do not find contestant’s culinary combats comforting, occasionally I just sit and watch. You see, I like food.

The basic premise of the program is who cooks best in given time. To tell people how much time is left, they use a clock that indicates how much time is left (much like a stop clock, with a small twist).

One day, while watching such intense battle, my mind went

It be cool to make such a clock using hmm… Excel?

While I cannot share my snapper (or pretty much any other food item) with you, I can share my Masterchef style Excel clock with you. So behold,

Ever looked at a Pivot table & wondered how you can sort it differently?

“If only I could show this report of monthly sales such that our best months are on top!”

Well, there is a way to do it without sacrificing 2 goats or pleasing the office Excel god. Just use custom sorting options in Pivot tables.

Finally, I got some time to sit down and do what I love most – write a blog post to make you awesome in Excel. After a whirlwind trip to Sydney, I am back in India to spend few days with my kids & wife before rushing to Australia to run 2nd leg of my training programs (in Perth, Melbourne & Brisbane). I did 2 sessions in Sydney – one for KPMG and other for public and both went very well. We got lots of positive feedback and people really loved it. I am saving the details for another post, but today lets talk about Interactive Sales Chart using Excel.

Take a look at the Interactive Sales Chart

First, take a look at interactive sales chart. Today, you will learn how to build this using Excel.

Welcome back. In final part of Making a Customer Service Dashboard using Excel let us learn how to add macros & VBA code that makes our dashboard interactive.

As you can see, there are 2 important macros in this dashboard.

Ever wondered how we can use Excel to send emails thru Outlook? In this article we well learn how to use VBA and Microsoft Outlook to send emails with your reports as attachment.

Scenario: We have an excel based reporting template. We want to update this template using VBA code to create a static version and email it to a list of people. We will define the recipient list in a separate sheet.

Read on…

Often we have 2 workbooks with same data structure but different data. We want to compare both and see how they differ. Lets talk about view side by side mode in Excel and how we can use it in situations like these.

Last week, we learned how to use SQL and query data inside Excel. This week, lets talk about how we can use VBA to consolidate multiple data sheets from different workbooks into one single worksheet.

Often I have thought, if I could have write “Select EmployeeName From Sheet Where EmployeeID=123” and use this on my excel sheet, my life would be simpler. So today we will learn how to do this.

People spend a lot of time thinking whether to use Excel as their database or not. Eventually they start using Access or SQL Server etc.

Today we will learn how to use Excel as a Database and how we to use SQL statements to get what we want. We will learn how to build a form like above.

This week many Excel bloggers are celebrating VLOOKUP week. So I wanted to chip in and give you a comprehensive guide to VLOOKUP & Other lookup formulas. Read on …,

What is VLOOKUP Formula & how to use it?

I often tell my excel school students that learning VLOOKUP formulas will change your basic approach towards data. You will suddenly feel that you have discovered a superman cape in your attic. It is that awesome.

Did you know you can apply any text effect to a single character or group of text characters within a cell ?

This post will show you 2 quick tips to assist you in this technique.

Sawadee Krup folks. Today, we begin a new series on Chandoo.org – Making a Customer Service Dashboard using Excel. This 4 part tutorial teaches you,

Designing Customer Service Dashboard
Data and Calculations for the Dashboard
Creating the dashboard in Excel
Adding Macros & Final touches

Customer service is one area where a lot of data is collected regularly. Understanding all this and making business decisions is quite complex task. This is where dashboards shine.

Read on to learn how to design customer service dashboards.

Here is a quick tip to add awesome to your Wednesday.

If you want to enter numbers like 00023 or 023.340 or 23.34500 in your Excel sheet, you would notice that Excel magically removes leading zeros and trailing zeros (after decimal point) as the number 23 is same as 00023. But sometime, we want 00023, not 23. Then what?!?

Very simple, we use TEXT format instead of number format. Just select the cells where you are going to enter these numbers, and from Home ribbon > Number area, select “Text” as cell type. This tells Excel to treat any value you enter as Text, not as number. So when you type 00023, it will appear as 00023.