Webinar Recap: Data Analysis Using Project with Excel

Please find a transcription of the audio portion of Jeff Bongiovani’s Data Analysis Using Project with Excel webinar being provided by MPUG for the convenience of our members. You may wish to use this transcript for the purposes of self-paced learning, searching for specific information, and/or performing a quick review of webinar content. There may be exclusions such as those steps included in product demonstrations. Watch the complete webinar on-demand at your convenience.


Data Analysis Using Project with Excel
Posted: 11/28/18
Presenter: Jeff Bongiovani
Moderator: Kyle


Welcome aboard. I’m going to be talking to you about data analysis using Project with Excel. Just to give you a general overview or topic outline for today…the general information, just a couple of little slides here at the front but then we have 3 major sections. Database and analysis concepts, adapting MS Project and of course MS Excel analysis.


So my intentions for today is, really number 1, understanding how to manage data in MS Project intended for an ongoing data collection in MS Excel. And then how that ties in to how you can modify MS Project and customize views, tables and fields. To help that to happen. And ultimately, how you can maintain that ongoing data collection in MS Excel for the purposes of an analysis. To that end, of the things that I wanted to say right off the bat, just to give you a disclaimer, it does require a relatively advanced understanding of Project features so if you haven’t customized fields before, if you haven’t necessarily worked with custom tables, templates, we’re going to review a couple of those concepts. Likewise, it’s just a general overview because there’s only so much you can do in an hour but an overview of Project views, tables, custom fields, Excel tables, functions and Pivot tables. And how all of those combine to help modify or create a process for you. So you may not find the exact example I’m going through in the overall process that I put forth useful, applicable in your own work but hopefully some piece of part of this is going to be helpful to you. So you might have some tips or techniques that you might pick up along the way.


Let’s get into database concepts. So I don’t know how many of you have designed relational databases before, if it’s something you aspire to, you have dreams of doing so or you’re quite bored of doing it but here’s some really important information before we get started into Project as well as Excel. So, a relational database means that you have interconnected information of tables. Typically, a relational database represents a central transaction or activity. So the example that I’ve provided below, you can imagine they’re ordering things. And surrounding these orders…people walking into stores, ordering things, what do you have? You have all of these different players involved in that particular game. Now if someone were to collect data, information about this transaction of an order, the best thing to do is to separate it into individual tables. Each representing the people, places, things, concepts, classifications and activities in their own separate tables. Of course, the information that we would want to collect is the stuff of reports. Whatever we want to generate as far as analysis. Breaking down information, etc. So the main focus here though is that each bit of information is stored only one time in its respective table and that tables will reference unique identifiers of other tables if they need to make a reference. So to break this down to make a relational database, number 1, we look at this relationship to that of a parent and child. You can store information about all of these different orders, dates, we’ve got information about how much somebody spent on that…but nonetheless, every single time that “good ole Mike” comes back to place an order, what do we do? Well, we don’t just say Mike or Michael or we say “oh, that guy”, you know that set of thing. We have an absolute identifier that we can actually get back to in reference to the customers. So if we think about a parent to child relationship [?] know you have one, when several customers may place orders over time, a single customer could return multiple times and of course a very important thing, only one customer name appears on the order. In that sense you don’t have like a gang of people just showing up “hey, we’d all like to order this”, we only need one credit card. For the most part, every time that same customer places an order, we refer to that same identifier. Think about this situation, a join or a junction relationship. Many to many, right? If we asked the question and I always did this in my database design classes, if we ask the question “how many x for each y? How many orders for each product? How many was this product ordered?”. One of the things that happens when you go back and forth, well we may have multiple products on each order and multiple orders of each product so how do we reconcile that? With something that’s called a “join or junction table”. You can’t necessarily repeat the same order number multiple times in the order’s table, that would be bad form. Same thing with products, you’re not going to type in same product profile multiple times. Instead what you create is some intermediary table where you show the many [?] like the example here. Order 1001, they ordered some wine, they’ve got some cheese, order 1002, they’ve got some cheese, this is quite a party. But for the most part, I can repeat the order number multiple times and the product number multiple times but never the same combination multiple times. If we think about the relational database in the sense of how everything interconnects, there’s potential analysis here. You can see that the customers are connected with orders, right? So if I’m able to do some kind of grouping, right, some aggregation of numbers, what I’d probably do is count the number of times customers placed orders, number of customers within each month and show the full name. Because they’re interconnected, I can use data from both of those tables. Orders is connected to order line items so I can sum each order total. And not only that but customers, by means of orders, is connected to order line items so I can actually analyze customer purchasing preferences. How often, what they buy and how much they buy at a time. So in that sense, if we think about how analysis is actually conducted, one of the things we look at is the repetition of any like item through a particular column. For example, on the top left corner where it has orders, I have a column for customers so if I’m able to consolidate into one row, all the like-customers, A, B, C, D, E, F, G…and then also count up or aggregate some kind of count or sum. While in the sense of what’s over on the right hand side, what I can really do and this is actually derived from that example, you can count up the total number of times Mike came back, January and February sum total. It really is all about the grouping and the numbers and dates and aggregates with the functions. So here’s the question: how is this even relevant to MS Project? Well, every time you create a brand new project file, what do you have? You have a database, an individual, separate database. How nice. So if you think about it, you have multiple tasks needing to be performed. Multiple resources may be needed to perform those tasks. For each individual task, multiple resources may be assigned and each resource may be assigned to multiple tasks. What do we have? We have a join and junction table that’s called assignments bringing together tasks and resources. If we think about it in MS Project when we’re trying to pursue that data, what do we have? We have task views, gantt chart, task sheet, that gets me in touch with task information. On the other hand, resource views like resource sheet. Resources, for the most part, can be listed out and of course itemized there. Now what are they called when they’re combined? Of course, assignments? Now I don’t remember any assignment views, right? Literally assignment views. But we get to see the assignment related data through task usage or resource usage. Now anyone who as ever used MS Project in the past designing their own custom views knows that nice little screen can fill out and ask do you want a single or combination view followed by…I get to pick a screen, I get a data table. Of course along with that view you don’t pick it right up front but it’s stored inevitably with that view if you change the text styles like the conditional formatting applied on a [?] tables. Bar styles. So if you have a fancy side order of gantt chart or tracking gantt, how would we make those different bars and appearances and things like that? Other things like print setup. Every single view has its own print setup. Now driving more towards the data side of things, you see I’ve bolded here, there’s on the screen always a data focus. So if you’ve used something like gantt chart or something like that, it’s going to be task focused. So if you’re going to use the resource sheet it’s got to be resource focused. If you use something like resource usage or you use task usage, its going to be assignment based. We don’t literally see a data table of assignments but when you go to those particular views, what do you see? Well, the default data table which is usage. In most cases, that’s easy to remember. What does it do [?] it demonstrates with task usage. Task as a priority and breaking down each of the assignments or in resource usage, it has an outline by resource and then where they happen to be assigned. So you still have the data, it’s there but it’s presented in more of an {?] format.

So the table concept. Why? Because if we’re going to do data analysis in MS Excel, we’re going to have to use tables, right? Because that’s how we’re getting the data. But for the most part, table concept in MS Project is kind of interesting. Anyone who tries to right-click insert a column will be faced with HUGE column of choices. Work percentage, attribute cost, what does it all mean? If only I knew all of them, right? 20 million fields. For the most part, when you encounter tables in MS Project though, what it really is, is a collection of just hand picked different columns. Out of the full list. Doesn’t have to be all of them. There’s no gigantic table that spans all of the 20,000 data columns because that would bd e too much. Instead, what MS Project does…here’s a nice little cheat sheet. Hopefully we’ll have this presentation downloadable for you but in this case, the existing project tables…think about this. Your default table, when you pull open MS Project, what happens? You’ve got gantt chart view, comes along with a side order of entry table, that’s the default table. Now you have the opportunity to switch the table over any time you want. Over on the left hand side, I have all of the data columns that are really worth seeing that drive the data entry as well as data analysis and across the top, what do we have? The different states of our data which is our current expectation based on whatever we plan or what have happen or what has happened. Our original expectation baseline and actuals and comparisons. In which case, I can open up my task usage view which is going to give me all of my assignment related data or data focus but if I want to switch over to cost or I see those data columns that are most relevant to cost. As you can see, I pull up the work table, you’ve got work all across the board. You’ve got cost, cost all the way across but it’ll show it broken down to that assignment level. So what comes into focus here is, if I want to make use of the data, I have to understand how Project is demonstrating or explaining that data and how I have access to the data tables that I can do data entry in and derive calculations from. Now the good news is, MS Project provides us the opportunity for custom fields, right? Text columns, numbered columns, calculations, all sorts of wonderful things. There’s a limit. Unfortunately, you can only have [jokingly] up to 30 text related columns. Of course I’ve met with customers and students before who complain very wildly about the limits of 30. But nevertheless, if we needed to do something that was solution related and I wanted aggregate data by my own choosing, not anything MS Project came along with by default. I could create those columns. I could create those, I could create my own calculations that would help me out. Of course then I could insert them into tables or customize my own tables, to grab access to them.


So let’s make a switch here. So my journey through the state of Maryland in the project capacity that it was…this example that I’m presenting to you was a dream, an aspiration. It wasn’t something that unfortunately ever got off the ground but it was something that was in the works with planning. Essentially the team that I worked on, we worked on cleaning up backlogs of cases. So if somebody is applying for food supplements, medical assistance or something like that, they apply for it but also, every year or so, every “X” number of months, they would have to get a redetermination. What would happen unfortunately is, the branch offices, they’d get an influx of all of the redeterminations. Too much for their regular staff to handle. So this team that I was a part of would step into the branch offices and we would help to clean it up in time. So if you can think about it, it’s not just about the one project that we’re running but we wanted to look at it in a bigger scope. We wanted to be able to see, over time, the number of projects performed for each one of the branch offices and how many times a year there was an occurrence of backlogs. So you think about the demands and the needs for reporting were much larger and very specific to this particular team. Now, in that sense, I wanted to talk to you first [?] right? So, let’s talk about some solution limitations that I was faced with. Number 1, there was no MS Project Server. The be all, end all, cure all for a lot of the situations where you can cross compare multiple projects. We unfortunately couldn’t do that. The data wasn’t going to be imported into Microsoft Access. Every time I mentioned that application name, everybody just kind of ran out of the room. That wasn’t going to be feasible and it’s going to find itself back into Excel anyway because MS Access, where as it can organize stuff really well doesn’t necessarily make really pretty charts and analyses and things like that. The existing MS Project export options were not up to the satisfaction of the team. [?] complicated. The export mapping, the visual reports. The reason why I said it doesn’t satisfy the simple process [?] the team, when I told them “hey, you can [?] a table and copy and paste it”, they were like “let’s do that”. So it wasn’t something where, “oh, by the way, you have to go through save as, export mapping, select this-“, they didn’t want to be involved with all of that. The shared template. Rather than updating everybody’s global.mpt, one of the things was that we may have wanted to make changes throughout time so it was a lot easier to share a template on a drive rather than update global.mpt. And sorry programmers, no VBA this time. We weren’t going to use that, we weren’t allowed to use that. So here’s what it looked like…the importing process. We have a customized table in MS Project containing the same fields as the matching Excel table with just copy and paste. What could be a problem with that? Well, let’s talk about a few things there…in a second. So this was selected. Why? Number 1, we still wanted to use MS Project to track and plan and update the project progress. And of course, MS Project has a lot of glorious wonderful reports you can customize there. That’s a different session altogether, this is more about MS Excel but for the most part, you can show time off and things like that, Excel’s not going to be [?] about that. And of course this project template was going to contain something that was easy to copy and paste after the project was completed. The reason why I stress after is that you can do it during the project but you’re going to have to figure out a way to timestamp a column to say this was the last export which is not such a bad idea if you want to say “here are all the alterations and changes the project may have overcome and how this evolved”. That would be really cool. Then MS Excel data collection contains all completed project tasks and of course made it feasible to create summary reports by branch office, case types, this is what we were looking for. So we can see what the dream was. The director wanted to be able to pull up a table and say “wow, this is what’s happened over the last 2, 3 years!” for each one of the branch offices.


So, let’s talk a little about adapting MS Project. Number 1, if you think about it, since we really wanted to study all the branch office information, projects, individual projects, case types…one of the things, just to tell you about our process, we would encounter a backlog project…number 1, there was a month to be backlogged. So say for example, we’re going into that, it’s November now, it’s pretty late in November for that but we would get all the cases backlogged from October. It could be possible they had stuff from August and September and stuff like that but as we were completing the project, imagine they’re also getting inundated with new forms. So we may have another backlog on our hands if we don’t also help to clean up the current log. So that’s very important because one of the things to show was in the summary report, did you clear everything that was before the previous month and that was very important to the branch manager. So you think back to what I was talking about, collapsing, consolidating to one row and doing some kind of aggregation. You’re going to want to do that or we wanted to do that by branch office, By the project date, by the case types in order to deliver those cool spreadsheets and pivot table charts. On the other hand, data we wanted to aggregate or the total count on the cases per each one of those things in the processing of the hours. Not such a bad thing. Now, to get this done, first of all, we of course encounter the creating custom fields you find on the project tab. By clicking on custom fields, what do you get? Well, you get this beautiful little window. Now I tried to highlight here the path of action but number 1, when it came to things like the branch office as well as the case type, we didn’t want anybody on the project to type just willy nilly anything they wanted. You’ve got to remember consistency, consistent data entry is what’s going to help to build the data analysis process. It’s going to help to enable sorting, filtering and the pivot tables. You want that on rails as much as possible. So what you can do, you can pick text and you can select whatever is available. You can see here in the examples screenshot I’ve got text 1, 2, and 3 devoted to branch office, project name and case type. And of course, in order to build each one of those names, I click rename and the lookup column or dropdown menu, so they select 1 and only 1, would be through lookup. Now, that’s a longer story, how to build the lookup, things like that. There are plenty of techniques but one of the things we’re going to lookup is that “no, you’ve got a [?] list, so they have to choose something in that list, they can’t just type in anything”. Hopefully, you provide them the complete list. On the other hand, when it came to calculated fields, this one is a little bit more complicated. Those of you who has experience doing formulas in any capacity through MS Access, know that column names can be created in brackets, that’s how they’re referenced in most cases. But for cases processed, here’s the interesting part. We’ve gotta figure, I wanted to calculate cases processed rather than calculate remaining cases because what I wanted the feasibility to do was to be able to just change the remaining cases. Usually at the end of the day it’s like how many do we have left and then you can type in that number, try to get an estimate. The one that I’m showcasing here is probably the most complicated, right? It’s not really that complicated…but the estimated remaining hours. You figure the way that we would estimate how many remaining hours there were was based on how many cases on average they could knock out per hour and of course dividing that, dividing that many cases by that number. So essentially you could 3 or 4, let’s say 4 cases per hour and you have 400 cases, guess what? There’s 100 hours remaining and of course that depends on the number of people [?] as well. The means by which to track and manage the project…then also coincides because think about this, at the end of it, the cases processed is going to be completed. That’s going to be the 0 remaining cases, the total amount and that number is going to be fed into MS Excel in the same case where we have the total cases. So, with that said, customizing tables. Once you’ve got your columns scored away, the easiest way I found in MS Project, you can go the formal way, I say you find a table that you love, right? You spend the time right-clicking, inserting columns, moving around however you will and then you’ll ultimately go up to your tables, menu on your view tab, click save fields as new table, name it, do whatever you want, treat it with love but for the most part you go back to the entry table and reset it. Kind of the cheat way of doing it but for the most part, it allows the fast, easy ways to create and save tables that you happen to be working on. Thank you Microsoft for adding that menu for us.


So one of the other things that I wanted to talk to you about, and remember earlier on you may say “this process doesn’t fit with me but some nice little tidbits along the way”-the default date format for MS Project always puts the abbreviated name of the day of the week in front of the date. If you’ve ever tried to copy and paste that into MS Excel, what happens? Well, it turns it into a text field. So in other words, you’re not going to be able to get the date aspect of it, it turns it into text. It’s not going to sort, it’s going to sort all the days of the weeks first so one recommendation to you is the only, sole place you can go to make a change to this…it’s not like Excel where you can highlight the column and hit format and change the date, time and format. You’ve got it in the options within the file tab. Once you go into options, underneath general, changing the date format, something quite simple that MS Excel can read, interpret and [?] get that 1.28.09 and that should work for you no problem. The other thing, just as a side note. This doesn’t have to do with the table customizations but it does have to do with after making all of these modifications. One of the things you can do in options under save, in save templates, I always appreciate, it says default personal templates location. Well, gee, it’s personal to all of us. You can actually name the shared drive, the shared folder as long as they don’t get disconnected from the network. Whenever they click or run new project, they can select from that and that’s our way around having to update the global.mpt on everybody’s computer every single time there’s an update. I just want that nice little tip out there. So, we stick it in the oven and we want to bake and boom, here’s what we came up with. Gigantic table across the bottom, spread across two slides because I wanted you to be able to see it but you can imagine when we’re doing data entry, we’re going to talk about the limitations to my example at the very end of this session. But one of the things I wanted to spotlight and point out here was that there’s no outlying level. We just itemize each thing like it’s a data column. You’re going to have to do some manipulations, changes and switching around if you have, in fact, outlying levels. Being able to collapse or expand or filter out to a particular outlying level can help to produce a consolidated table like this. It just so happened that the style of the project was something that is the way that we can lay it out, it worked and it was something that we knew could work for us in the long run. We didn’t necessarily have the outline. But there you go. There’s branch office, that’s not what we called them, we didn’t call them branch A, B, and C but their identities have been protected. So we have branch office, the project name, the task name, the case type and the case month. The case month was important because if there were leftover cases in June or July, we wanted to know about it. So it wasn’t just the earlier or previous or current, we could see how many started to build up in any one of those cases. The other side of things, the calculated columns. So when we would start out, we’d count up all the cases, type in the number and we’d just copy and paste that over to remaining cases. And in which case, we could put in what we would expect how many cases could be processed [?} different types [?] hour and of course as we would deplete cases, we would type in whatever number of remaining cases there were and then [?] were processed. The estimated hours, there you go, there’s the division. As you can see here slightly rounded up but then what happened was, I wanted to show the remaining work, the duration and the start and the finish. That helped us to be able or would’ve helped us, sadly, just to get through all the project stuff.


So, now that the tables have lined, let’s start to talk about MS Excel. Now there are a couple of things about MS Excel that we need to know right off the bat that’s going to fit in with this process. Number 1, Excel is not a relational database. It can use formulas to reference unique identifiers in a related data collections. There’s no enforced data integrity meaning the table could be referencing an identifier that doesn’t exist. What project is that? We don’t know. The data validation is severely limited. In MS Access for example you have stuff that’s called input mask. Input mask. Somebody has to key in 3 numbers followed by 4 letters…yeah, you can create drop down menus, it’s a wonderful thing and then use the auto fill it down through the column and unfortunately there’s no true enforcement of unique identifiers-ALTHOUGH there’s a nifty, wonderful conditional formatting you can create to identify repeated items so you can correct them yourself. So the idea though is in MS Excel, if you’re trying to create a table in MS Excel, the user has to follow specific design methods for Excel to recognize that what they’ve created is a table. So you have to do what Excel considers to be a table in order for Excel to want it to be a table. Which is why when I teach a basic level MS Excel class, it’s about the data entry, formulas and things like that. The level 2 and 3 was about understanding the correct context and shape of your information that leads to more of the automated tools. So to understand what we needed to piece together in Excel to make this happen, cell references, right? Let’s understand cell reference. Cell references, sure, I can get a summary, right? I can add stuff up. Glorious, wonderful but a cell reference, if you ever know, If I start typing in 10, 11 or 12, I’m going to have to babysit that function, I’m going to have to update. So usually, if I put myself in the right frame of mind, look at it through the right lens, I’m thinking a range reference is basically I’m referencing something that’s going to be unchanging.


Number 2, of course you can use named ranges to help. You think about it, I’m copying and pasting data from MS Project into a [?]. Could I use a named range? Well, naming the range means I can substitute, instead of making the range reference, just [?] numbers. But still, you have to babysit, you gotta go to name manager, you have to update it if you’re going to add anything new to it or the nifty little trick. Let’s insert a couple of rows between the numbers. Yeah, while that’ll help you in some cases, it won’t help you in all.


Now the remedy for MS Excel is something that they added in 2007. Version 2007 debuted the open xml table or in other words, [?] table. Now it’s not all about just making it look pretty. In fact, when you take something that MS Excel deems as a table, essentially what you’re doing is making a named range that automatically expands. If you ever turn that off, go into the Excel options, underneath the options, underneath your auto correct options, you have to turn it back on. [?][?] Every single time you add new information down at the bottom of this “table”, what does it do? It automatically stands the table to compensate for whatever is [?]. Now, if you’ve ever worked with pivot tables, we’re going to talk about the concept of making the pivot table dependent on something like this rather than something that’s just free form. Making it dependent on this means you’ll never have to adjust the pivot tables reference to the range. You can feel free to create a bazillion different pivot tables and all you have to do is just click refresh on one of them and they get all the brand new information. So to make this table, you go to insert, click on table, works the data for your table and it’s done. And of course there’s a table tool’s tab where you can rename it and make all sorts of extra analysis out of it and do all sorts of fun things. But you may not need to rename it if you’re just doing one table. You’ll see here the formula has changed, right? That sum function is referencing table 1, in brackets, quantity [?]. But nevertheless, as the quantity column grows or changes, you don’t have to update that function. That updates automatically for you.


So how do I make a proper Excel table? Well, here’s the catch. A lot of people jump into MS Excel prioritizing the pretty and not the practical. The sad news is, a lot of times we add additional rows up across the top, we put up titles, we color coordinate, we merge cells together…yeah, that’s kind of a bad idea. The idea is that pivot tables, pivot charts are probably what most people want and if you situate your data just right, then you can create any visual representation that you want. Beginning the table in cell A1 with the first column [?] and all the rows there after with your data. It’s [?] one out. Using a single row for column headings, don’t double up, use wrap text and change the length of the column. Each column should contain only one type of data so one of the biggest issues and hurdles is that I see a lot of people have a [?] date. Instead of a date, they leave a little comment, “I don’t really know”. But the problem is, is that if you try to filter that column, MS Excel is going to be like “what? is this a date column? Is it a text column? You want to try to run formulas or functions on it?”. It’s going to have to be really crazy. So either it’s going to be a date or it’s going to be blank. If they’re numbers they’re numbers. Leave another column for comments but when it comes to data types, each column should have its own. Basically, your [?] up as if you had designed this table in Access or anywhere else. And don’t skip rows or columns within the list. Each row is going to be representative of a data item. You can create outlines and breakdowns in your pivot table. you’ll see a screenshot of that a little later. So, one thing to the next, here’s what happened. We fulfilled or we could’ve fulfilled but the dozens of times before where I have done this particular example and actually I’ve presented this as an example in a number of my Project classes. Here’s the idea: it’s a somewhat simple copy and paste. One of the things that MS Project started doing, when you would copy and paste the table into MS Excel, it now brings the column headings with it, even if you just select the cells in that table. But if I just simply copy and paste that in Excel, selected all the rows and pasted that down at the bottom of my own [?] table, what you get is this ongoing data list of all the necessary information. With the exception of having to delete it, it’s pretty simple. Running a custom export map, opening the exported file and pasting it to the Excel table was too many steps. When I talked it over with the team, it was too many steps, they just wanted copy and paste.


Let me give you another nice little tidbit. When copying and pasting into MS Excel, one of the things besides the dates, you can see start and finish, changing the options in MS Excel, not such a bad thing, cleared that issue right up. On the other hand, you look at stuff like work, remaining work and duration which is why it may be fun and exciting in MS Project to do different types of duration. You can enter it in as days or lapsed days, which is necessary in some cases, you can put it in weeks, you can put it in hours but if you keep consistent with the same label. One of the things you can do is use Excel functions to help you remove that label at the tail end of it. You can change the options in Project as well but I think you’re going to want to keep it so that when you’re printing out project reports, nobody is confused. Here you go, here’s some monstrosity and of course, once again, this will hopefully be available for download so you can come back, absorb it all or take a screenshot, do whatever you want. In MS Excel, there’s a lovely function that’s called “substitute”. So if I literally, in quotes, typed in “82 hrs”, “ hrs”, “”. What’s the object? 82 hours. What do you want to find? I want to find space hrs. What do I want to replace it with? Nothing. What’s it going to do? Well, it’s going to put 82 in the column. The 82 isn’t going to be a number just yet. Substitute is what’s called a text transforming function. You can feed it dates, you can feed it numbers, you can feed it all sorts of stuff. At the other end of it though, unfortunately, is just text. So I need another function, oh no, next set of functions. Value, text that should and can be a number knowing this doesn’t remove letters and other stuff that’s preventing it from being one. I try to be very descriptive about those little tool tops. If I put in quotes 82 for value, that would transform it into the literal number. What you’re looking at is that is a function. L2 or whatever cell contains the blah blah blah number 84 followed by space hr, if you use that function, it’ll clear out the hours, it’ll give you a number that you can sum up, you can add up. In action, this is what it’s going to look like. Look at the screenshot down at the bottom so I’m giving it the reference that L2, 9 hours, 84 hours, it just washed that hrs right out of it. So if I am planning on supplementing my pivot tables and my analysis and aggregating numbers and I want to make full use of the work column, I’ve got to get to that number. But it also means that I’m going to have to have these supplemental functions in order to make that happen. So the way to do that, honestly, the columns that you’re placing the data into, you take a look at that nice little blank table across the top. It should be on the left hand side. It makes it easiest for the enduser just to copy, paste, delete that one row with the column headings and all of the functions that fill automatically in data tables, over to the right. The red items. So your supplemental functions should go over to the right. So upon first pasting it into MS Excel, you define it as a data table, you set up your supplemental columns like that and what does that mean, once you’ve got that together after the first paste? Boom. It means we’re off to the races with pivot tables. The idea here is, you go to your insert tab, you click on pivot table after you define the import base table that you’ve created. Pivot table, it’s a fancy Microsoft term for automatic reports and or [?] and analysis. You can have as many pivot tables as you want connected to your data source. I know and I’ve taught students before that have said “okay, I’m going to lay out every standard report that I need to print out” and their job basically is, hit refresh, hit print, that’s about it. On the other hand, you may want to just have-here’s a blank pivot table and I can drag and drop whatever columns I want into the different places. Once you go through that process, there’s table 2, [unintelligible] but table 2, whatever table it’s named, where it’s going to be placed on a new worksheet, don’t put it on the same worksheet as the original data table. What’s going to happen is, you’ll get a blank spreadsheet for the most part, brand new worksheet and it’s going to have this beautiful, wonderful pivot table fields window on the right hand side. You can see, up towards the top, what do we have? Branch office, project dates, task name, case type, case month, resource names, total cases…so what in the world do we do with those? We drag and drop them into the areas towards the bottom. See, if I had something called project here, let’s say we’ve been doing this for the last 5, 6, 7 years, I can drag and drop project here, into the filters. And just filter out for all the projects that were done in 2018. That means, if I had a report for a particular branch office, just want to see branch office activity, different cases across one year, I would use the filters in the top left hand corner. Rows. Bottom left hand corner. Of course you get the idea, think about a spreadsheet or an [?]. What do you want to have grouped over on the left hand side, what do you want to have grouped across the top? All the rows, grouping together, consolidates over on the left, columns across the right. Basically, what would happen is, if you put number fields or even text fields, into values, it’s going to automatically aggregate the data. Then you have sums, you have counts, averages, maximums, minimums…you have standard deviation as well as variance both by population by the example set that you choose from and not only that but you can also display it in terms of percentage, out of the grand total. Percentage for row, percentage for column but the matter is just getting the value that you want into that area. Now, here’s the example, here’s what would happen. I took that example table and turned it into a pivot table. You can see how I’ve arranged it. Case month is across the top, row on the left hand side is the column for case type and this shows the sum of the total cases. So for type X, they had 25 that were backlogged from June, 250 backlogged from July, 60 backlogged from August giving us a grand total of 335 assuming that this was a one per month project. This right now has no filter so this is for all time, all the projects or all the months. You can see how that can start to add up. On the other hand, let’s see here. This is case type by month. Here’s the branch office across the top, case month over on the left hand side…sorry I don’t have multiple branch offices but you can get the idea of pivot table. Why did they call it pivot? Because I can move and shift things around. That’s the flexibility. Anybody who has sat and taken data and let’s copy, then paste, more coffee then copy and paste, move and sum, paste values and ship this over here…you’re working hard, you’re not working smart and in the case of case month, you’re able to just drag and drop a column and it does this automatically. And knowing fully well, I complete a project, I copy and I paste that down at the bottom of this grandiose table. It means, that in the long term, I could be looking at reports like this. I could be submitting this to the director, I could be submitting this to branch. And the more I have my hands on not just the individual project, getting that complete but the data that links all of those projects that I’m completing together, that gives us a better vision of exactly what could happen next, better predictability. Well that’s something that the [?] guide actually says. We do this so that it can be more predictable. What can help more than to be able to link data analysis tools with your MS Project.


In conclusion, I know that the example concentrated on task, what happened to resources, what happened to assignments, why only have an hour? You want to stick around for the next 6 hours most definitely! We’ll talk about it all day long. One of the things though, you could get assignment information, prorated timescale data is possible but it’s not simple. You’re going to have to use some coding if you’re not using some functions of formulas. You can export usage views but it’s far more complex. You’re going to have to actually fill in the blanks. I know this example is shaped perfectly to what I was talking about because why? I gave the task naming conventions, they worked out. There wasn’t an outline level, something like that. And not only that but it excludes other technologies. So having access to other technologies might alter this process. The overall thing though, remember what my intentions were it was to really look at-if you’re going to share information across both of the applications, you have to mold MS Project to think in terms of the destination of Excel. And not only that but once it gets to MS Excel, it can’t just haphazardly be any shape or form. It has to be very specifically something that Excel deems as a data table. Something that ultimately can be sorted, can be altered in workload. Which means, probably finding some kind of method or following a method that I prescribed here today.


One of the things that I know that sometimes we think about when we think of MS Excel analysis is, all of these charts! There’ll be explosions and fireworks and all sorts of wonderful things. The biggest stumbling block, honestly, through my entire teaching career, wasn’t how to operate pivot tables. It wasn’t necessarily how to create functions. It’s about establishing data integrity between multiple applications. That’s why I chose to really focus on that aspect of things rather than the fun and exciting part of it. I’m sorry it wasn’t…hopefully it was fun and exciting for some of you out there. But in any case, it is definitely something that I thought was noteworthy in talking about because you’ve got two great applications, you’ve got those tools on your desktop. So it’s far better to be able to do more than less.


Watch the on-demand recording


Avatar photo
Written by Jeff Bongiovani
Jeff Bongiovani is currently the Lead Course Developer for Edwards Performance Solutions who oversees the production and maintenance of courses on Project Management, Systems Engineering, Software Development, Business Process Improvement, and Cybersecurity. He is also a trainer with over 20,000 hours of classroom experience spanning 17 years.
Share This Post

Leave a Reply