Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces across the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. In the United States alone, millions of job advertisements requiring Excel skills are posted every day. Research by Burning Glass Technologies and Capital One shows that digitals skills lead to higher income and better employment opportunities at a time when digital skills job are growing much faster than non-digital jobs.
In this second course of our Excel specialization Excel Skills for Business you will build on the strong foundations of the Essentials course. Intermediate Skills I will expand your Excel knowledge to new horizons. You are going to discover a whole range of skills and techniques that will become a standard component of your everyday use of Excel. In this course, you will build a solid layer of more advanced skills so you can manage large datasets and create meaningful reports. These key techniques and tools will allow you to add a sophisticated layer of automation and efficiency to your everyday tasks in Excel.
Once again, we have brought together a great teaching team that will be with you every step of the way. Prashan and Nicky will guide you through each week (and I am even going to make a guest appearance in Week 5 to help you learn about my favourite tool in Excel - shh, no spoilers!). Work through each new challenge step-by-step and in no time you will surprise yourself by how far you have come. This time around, we are going to follow Uma's trials and tribulations as she is trying to find her feet in a new position in the fictitious company PushPin. For those of you who have done the Essentials course, you will already be familiar with the company. Working through her challenges which are all too common ones that we encounter everyday, will help you to more easily relate to the skills and techniques learned in each week and apply them to familiar and new contexts.

JE

I found the following areas to be particularly useful to learn about:\n\nCombining and Changing Text\n\nExtracting and Finding Text\n\nNamed Ranges\n\nCOUNTIFS and SUMIFS\n\nTables and Pivot Tables

HF

Nov 16, 2018

Filled StarFilled StarFilled StarFilled StarFilled Star

It was a good experience. I have learned many new tools and how to work smartly. i recommend every Student and employee to go through this course because excel makes work easier. Thanks Coursera.

From the lesson

Pivot Tables, Charts and Slicers

This module deep dives into the popular (and very useful) pivot tables. Learn how you can create and modify them to solve a variety of business problems. Then gain skills to create interactive dashboards with pivot charts and slicers.

Taught By

Dr Yvonne Breyer

Program Director, Design and Experience, Global MBA

Transcript

One of the things that makes pivot tables so powerful, is the ease with which they can be changed. And not just the data, but even how it is calculated and presented. In our last video, Uma created a pivot table showing us the total sales by customer type. Her manager however, has asked that she adjusts this. So rather than showing the total sales, it shows the number of orders. Really, all we're looking to do here is change it from a sum to a count, but we've seen that in Excel, pivot tables automatically sum values. Fortunately, this is very easy to change. Now, like with anything in Excel, there are a lot of different ways of doing this, but a really good one stop shop is a right click. So we're going to find one of our numeric values and right click. This brings up a menu of options that allow us to change our pivot table. And just after halfway down you'll see Summarize Value By, and of course it has been summed, but we could choose from a number of list functions including Average, Min, Max, or in this case, Count. So we're going to click Count, and there I have my number of orders by customer type. Let's just quickly recap that. So right click summarize values by and choose your function. Now, in my second pivot table, Uma's been asked to make a slightly different change. Instead of seeing the total sales as discrete values by year, our marketing manager would like to see them as a percentage of grand total, so he can more easily measure them against each other. Now again, very easy to do. Come to one of your numeric values and right click. This time come to Show Values As, and you'll see at the moment no calculation has been applied, but there's quite a selection we could choose from. Now we'll just look at a couple of these to give you an idea. In this case, we'd like to see percentage of grand total. So I'm going to click on that, and now we have each of our values as a percentage of our grand total. So, for example, Yvette's 2014 sales make up nearly five percent of our total revenue. What about if we wanted to get a breakdown by year? Well, similar process. Right click Show Values As, but this time we get to choose percentage of Column Total. What it has done is now added each column as 100 percent and we are getting a breakdown within that column. So Connor's sales for 2017 make up over 40 percent of the sales for that year. To get it back to our discrete values, simply right click, come down to Show Values As and set No Calculation. So there we saw how we could change the way the date was calculated. It's possible that we will also wish to format our pivot, and this can also easily be done. For example, these values should really be shown as currency. So we're going to right click on one of our values, come to Number Format, choose Currency and say OK. Looks much better. What about changing the way our pivot looks from an aesthetic point of view? Well, that we can do too. We have a whole selection of tools on our Design tab of our pivot table tools ribbon which will allow us to do this really quickly. Let's make a few tweaks to our table, though. So we would now like to see a breakdown of sales by year within region. So I'm going to get rid of my Account Manager. I'm going to drag it off the Rows area, and instead I'm going to put State into the Rows area and then I'm going to get my Order here, drag that from Columns, and put it into the Rows area. I now have a breakdown of years by state. You will notice that the minute you add two or more values to the columns or rows area you get subtotals. Now with the subtotals, you actually have a few choices. You can choose to turn them off. So by coming up to my Design tab my ribbon, come to Subtotals, you can switch them off or you could choose where you want them to display. Mine at the moment is showing at the bottom of my group, but I prefer to see them at the top. This gives me a slightly more compact report. You can also choose to switch your grand totals on or off. We're going to leave our as is, and you can make some amendments to report layout. By default, it is in Compact Form, but I can change it to Outline Form or even Tabular Form. If you select Tabular, you have a further option. You can choose to Repeat All Item Labels. This now looks like a regular table. It does take up a bit more space, though. So I'm going to go back to my Compact Form. You can also add some instant styling to your table, much like you could with regular tables, by coming to your Pivot Table Styles, and if you click the More button, the little dropdown on the far right, you get an even bigger selection, and I'm going to go for some nice quick styling. So we've now seen how easy it is to adjust the data, the way it is calculated, and even the formatting in a pivot table. In the next video, we're going to further refine our pivot table by looking at how we can group, sort, and filter the data.

Explore our Catalog

Join for free and get personalized recommendations, updates and offers.