Home / Business & Management / Big Data & Analytics / How to Use Microsoft Excel for Data Analysis / Reporting Data in Excel
Reporting Data in Excel
.
Hello, welcome everyone. My name is Dany Hoter. I’m a senior programme manager in the Excel team. And this course, it’s an introduction to data analysis using Excel. It’s a part of a series of courses that we are recording and making available. The first one we did was called Analysing and Visualising Data in Excel, and you can search for it. My recommendation will be to take this one. Unless you are very familiar with this content, you can jump right away to the second one. Otherwise, take this one, and then continue to the next course. And I will mention the next course, also, in the continuation of this course. So in this course, I’m going to play two roles.
I’m going to be myself. I’m going to be Dany, the Excel programme manager, explaining things about Excel and how to use it. And I’m going to be another Dany, which is a business person in a company that is selling bicycles, and selling components for bicycles, and clothing for bicycles in different countries. And the role of me as Dany, the business person, is to present the sales information and the company to a new sales manager that we have.
Her name is Lucy, and she is new to the company, and I was asked to introduce her to the company sales, so that she would be able to understand what we are all about, and, probably, make decisions based on this data as she go on to go forward. And if I switch to my machine– this is actually what I was thinking of presenting for her. So I was thinking of taking some aspects along some axes along the year, so show sales by year for each category of product that we sell, sales by country, and sales by product category altogether, different types of charts, to show the performance of our company.
And in order to do that, I approach a third person in our company, called Jack. He’s my friend in IT, and he usually is the person I’m going to get the numbers I need to create reports, usually. So I explain to Jack what I need. And this is what he send me. Summary data, according to what I was asking, by year, and category, by country and year, and just by category, which could be the basis for these three charts that I was attempting to include in my report. So as I get these numbers in Excel, I was thinking of inserting the charts.
I go to Insert Chart, and I see that there is something that looks promising, called Insert Recommend Charts. So just before we go forward, I will just to mention something about the version I’m using. I’m using Excel 2016. I’m using Excel 2016 coming from Office 365 as a subscriber. So I have all the feature that were released with Excel 2016, plus some additional ones that I will point to as we go forward that were delivered after the release of 2016. If you use earlier versions, like 2013 or 2010, you’ll be perfectly fine. You will find almost everything I’m showing here. Maybe a few things here and there, you will not find in your version.
I will try to point to them. But other than that, you’ll be fine using these other versions. So recommended charts– I’m parking here in the general area of this data, and I’m looking at the recommended charts. I see that there’s one chart, there’s another. This looks interesting, like showing me a stack of the sales. But I see that I have the right information, only it’s reversed. I have each column represents a category, and I wanted each column to represent a year, and to see the composition of the sales for the year for the category. So I can go here to switch between rows and columns.
And now, it actually shows me a column for each year and the categories as different colours, which looks exactly what I wanted. I go to the second set, Insert, Recommended Chart. I park here. I insert the chart. And this is actually showing me the columns for each year within each country. So we see the trend, a year to year trend. I see that, for example, in Australia, there was some drop of sales year to year, while in other countries, we are doing much better, and these sales are growing, which is something that probably Lucy would be very interested to understand better. And the last part is, again, I’m going to use Insert and Recommend Charts.
And here, my initial plan was to use a pie chart, which is a good way to visualise these numbers that add up together to 100%. Sales by accessories, sales by bikes, and by clothing. So this is the general idea of the report I was planning to show Lucy in order for the introduction. And I went with this to Lucy to show her the numbers and the charts. And to tell you, to be frank with you, this was not really a big success with her. She was happy about the total numbers, but she immediately had more questions about more detail and different ways to represent the data and to show the data.
She was interested in data by subcategory, and not just by category. She was interested in profitability, and not just by the sales number, because we might be selling a lot, but maybe the profit is not there. And also, she wanted to understand a lot more about the customers, like what ages are they? Can we bucket the customers by age group, and report by age group? So she had tonnes of tonnes of other questions. So each time she wanted something, I actually had to go back to my buddy Jack in IT and ask him for a different set of summary numbers.
So by the fifth time, I actually went through this exercise, Jack got tired of preparing the numbers again and again. And he told me, look, I can see that what you need is something else. I will prepare for you a set of the detailed data of the sales of these three years, and using this detailed data, you will be able to create any kind of report or summaries that you want, and you can show different aspects of the business.
And more than that, you will be able to give it to Lucy, and explain to her how she can use the data to create the summary she wants the different comparisons, on different axes, and so on based on this set of data. And if this is going to work out, we can produce new sets of data with the same kind of detail going forward. And this is the way we can monitor the performance going forward. And so this is what we are going to do. I’m waiting for the detailed data to come. And just one more advice that Jack gave me, because he’s pretty knowledgeable about using Excel.
He said, what you really need is to create pivot tables, and to use also Excel tables. I knew a little bit about pivot tables. Excel tables was something I didn’t know about. So this is something I will have to look at. And so I’m waiting for the detailed data. And we’ll see you in the next part, when we receive the data and start exploring it, using pivot tables and tables.
Microsoft Excel has a vast number of features and is a valuable spreadsheet programme for many businesses.
Microsoft Excel is a powerful reporting tool that allows you to do mass arithmetic calculations, compare lists and values and visualise your data.
You can create charts for analyses which provide a visual representation of situations, or you can use Excel to pool all your data in one place.
Amongst other things, Microsoft Excel is used in businesses to present financial information, record income and expenditures, chart data and much more.
When it comes to data analysis and documentation, Microsoft Excel is a helpful and resourceful programme.
Let’s take a look at the following video tutorial where Dany Hoter, senior project manager in the Excel team at Microsoft, introduces us to ways in which business data can be reported in Excel.
Join the discussion
Tell us about your experiences with Microsoft Excel. Have you used it in the past and if so, for what in particular?Use the Discussion section below and let us know your thoughts. Try to respond to at least one other post and once you’re happy with your contribution, click the Mark as complete button to move on to the next step.
This article is from the online course:
How to Use Microsoft Excel for Data Analysis
Created by
This article is from the free online
How to Use Microsoft Excel for Data Analysis
Reach your personal and professional goals
Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.
Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.
