Export a Plan to Microsoft Excel

Locked lesson.

Upgrade

About this lesson

Know how to export a plan to Microsoft Excel for the purpose of deeper analysis.

Quick reference

Export a Plan to Microsoft Excel

Export plan data to Microsoft Excel and analyze it using Pivot Tables and Pivot Charts. 

When to use

We export plan data to Excel whenever we need to perform a deeper analysis of plan data and create reports and charts using Excel's vast array of data analysis tools.  

Instructions

We can export our plan information to Microsoft Excel so we can analyze it. For example, maybe we would like to create a Pivot Table and a Pivot Chart in Excel to analyze the number of tasks in progress by label.

Or maybe we want to create a chart that shows how many tasks we have assigned to each bucket. 

Export a Plan to Microsoft Excel

  • Click the three dots at the top next to Schedule. 
  • Click Export plan to Excel. 

The plan will be exported to the Downloads folder unless you have changed this setting in Windows. 

  • Navigate to the Downloads folder in File Explorer. 

The exported Excel file will reflect the name of the plan and have a file extension of .xlsx. 

  • Double-click to open the Excel file. 

Analyze the Data

The Excel file will contain all exported data from the plan. This includes columns that contain the task name, bucket name, progress, priority, assigned to, created by, created date, etc. 

Once we have our data in this format we can tidy it up and start creating our analysis. 

Remove Unnecessary Columns

Start by removing any columns that aren't needed in the analysis.

  • Click on the column header to select the column.  
  • Right-click the mouse and select Delete. 
  • Repeat this process to remove other columns that aren't needed in the analysis. 
Format Data as a Table

Next, consider putting the data in a table. 

  • Click anywhere in the data. 
  • Press CTRL+T. 
  • Check the data range is correct and make sure the option My table has headers is selected. 
  • Click OK. 
Create a Pivot Table Report

We can then go a stage further and create Pivot Tables and Pivot Charts to analyze our data further.

  • From the Table Design ribbon, click Summarize with PivotTable. 
  • Select New Worksheet. 
  • Click OK. 
  • Drag and drop the Pivot Table fields into the Filters, Columns, Rows, and Values areas to create a report. 
Create a Pivot Chart
  • From the Pivot Table Analyze ribbon, click Pivot Chart. 
  • Choose a chart type. 
  • Click OK. 
  • Format the chart as desired. 

If we need to present our analysis to stakeholders or our team, we could even create a dashboard in Excel to display the key metrics. 

Login to download
  • 00:04 A really useful feature of Microsoft Planner is the ability to
  • 00:08 export the plan details out to Excel.
  • 00:12 Now, why might you want to do this?
  • 00:14 Well, if you already use Excel, you know that it has a wealth of analysis tools.
  • 00:21 And it might be that you want to create, maybe, a pivot table, or
  • 00:25 maybe you want to display some of the plan metrics in the form of charts to
  • 00:30 present to your CEO or stakeholders.
  • 00:33 So being able to export all of the data for your plan out to an application like
  • 00:38 Excel, where you can further manipulate it and analyze it, is a really great feature.
  • 00:44 And we can do this with ease,
  • 00:46 simply because everything is located within Microsoft 365.
  • 00:50 So let's take a look at how we can export this particular plan out to
  • 00:55 Microsoft Excel.
  • 00:56 And then I'll just show you a couple of things in Excel that you could do to,
  • 01:00 not only tidy up the data, but perform a deeper analysis.
  • 01:04 We're not going to go too much into it because this isn't an Excel training
  • 01:08 session, but I really just want to introduce you to some of
  • 01:11 the capabilities so you can see why it might be advantageous to do this.
  • 01:15 So with your plan open in Planner, we want to jump up to the three dots at the top.
  • 01:21 And we have an option in here, Export plan to Excel.
  • 01:25 Let's click on this.
  • 01:27 Now, notice in the top right-hand corner, it's automatically exported it and
  • 01:33 downloaded an XLS file of all of my plan data.
  • 01:37 And this goes straight into my Downloads folder.
  • 01:39 And it should do for you, unless you have changed your settings within Windows so
  • 01:44 that all your downloads go to a different folder.
  • 01:48 So, what I'm trying to say is, wherever your downloaded files normally go to,
  • 01:51 this is where this file is going to live, as well.
  • 01:54 Now I'm simply going to click Open file.
  • 01:56 So there we go.
  • 01:58 Now the first thing I'm going to do here is I'm going to click on Enable Editing so
  • 02:02 I can start to make some changes to this file.
  • 02:05 Now you can see here all of the data that's been exported out.
  • 02:09 We've got Task ID, Task Name, Bucket Name, Progress, Priority, Assigned To,
  • 02:13 all of the information that we've added to our cards.
  • 02:16 And if I scroll across you can see, where we have information in the card,
  • 02:21 we have different columns.
  • 02:22 Now the first thing I would probably do here, is I would go through and
  • 02:26 start tidying up this data.
  • 02:27 Because it might be that I don't necessarily want all of these columns
  • 02:31 included in my analysis.
  • 02:32 So the first thing I'm going to do here is just remove the Task ID column because I
  • 02:36 don't really want that.
  • 02:37 These ones are all fine, but I do have some blank columns at the end here.
  • 02:42 So where I haven't actually completed any data.
  • 02:46 So let's get rid of all of these.
  • 02:49 Let's right-click and Delete.
  • 02:51 So that's all looking pretty good.
  • 02:53 I can see here, for Task Name, I've got some indentations going on in here.
  • 02:58 So if I want to quickly tidy that up to make it easier to analyze,
  • 03:02 I'm going to insert a helper column.
  • 03:04 And we're going to run a little Excel function called TRIM.
  • 03:09 Now, what that's going to do is, it's going to trim out any erroneous spaces.
  • 03:14 So where we have that kind of indentation at the beginning there,
  • 03:18 it's going to remove that.
  • 03:19 So I can then double click to copy this down so everything is a little bit neater.
  • 03:24 Now, if I was to now go in and delete out this column, I'm going to get some errors
  • 03:29 just here, because the column that the formula refers to has now been deleted.
  • 03:34 So a little trick here, if you use Excel,
  • 03:36 let's do Ctrl+Shift+Down to select all of the data in that column.
  • 03:41 I'm going to press Ctrl+C to copy in, and
  • 03:43 then I'm going to Paste just the values directly over the top.
  • 03:48 So, what that essentially does is it throws away that underlying formula.
  • 03:52 So it now means that I can simply delete out this first column, and
  • 03:55 I'm not going to get those errors.
  • 03:57 Let's add the heading back in.
  • 03:59 So this is Task Name.
  • 04:01 And now that's a lot neater for me to analyze.
  • 04:03 The final thing I would probably do here is, I would put this into a table.
  • 04:08 So let's click in our data, Ctrl+T.
  • 04:11 Yes, my table has headers.
  • 04:12 Those are in the first row, let's click on OK.
  • 04:16 And now I'm pretty much ready to analyze this data.
  • 04:20 So if I was putting this into something like a PivotTable,
  • 04:23 I can jump up to Summarize with PivotTable.
  • 04:26 I'm going to use my Table Range, I'm going to put it on a New Worksheet.
  • 04:30 Let's click on OK.
  • 04:32 Now if you're not familiar with Excel PivotTables, don't worry too much.
  • 04:35 This is really just to show you the potential when it comes to analyzing your
  • 04:39 plan data.
  • 04:41 So, what I could do is I could produce a PivotTable report,
  • 04:45 which tells me how many tasks I have assigned to specific buckets.
  • 04:50 So I'm going to grab the Task Name, I'm going to drop that into Values.
  • 04:53 I'm going to grab Bucket Name, I'm going to drop that into Rows.
  • 04:56 And now you can see how many tasks belong to each of these buckets.
  • 05:01 I could switch out Bucket Name, and I could maybe grab Progress and
  • 05:04 drop that into Rows.
  • 05:06 So I can see that I have 1 that's in progress, and
  • 05:08 47 tasks that haven't been started.
  • 05:11 And I could carry on building my analysis.
  • 05:13 So, maybe, I want to add Bucket Name and Progress.
  • 05:16 Let's put Bucket Name in columns like so.
  • 05:19 I could drag Bucket Name into rows, as well,
  • 05:22 to get a completely different style of report.
  • 05:24 Now, I'm going to remove Progress.
  • 05:26 So we just have the Count by Bucket Name.
  • 05:28 And from here, if I wanted to create a chart, I could go directly over to
  • 05:32 PivotChart, and then choose the type of chart that I want to insert.
  • 05:35 So let's just do a basic column chart, and there we go.
  • 05:39 I can now go in and start formatting my chart to make it look a little bit nicer.
  • 05:46 Now, as I said, this isn't a full on Excel training session.
  • 05:49 But, hopefully, that gives you an idea as to why it can be really useful
  • 05:54 to export your plan data out, and then perform a deeper analysis.

Lesson notes are only available for subscribers.

Gift this course