PM Tech Corner 

Great New Features from Excel

Welcome to the Technical Corner for PMI Portland Chapter.  This news article section is designed to help Project to Portfolio managers leverage tools, technologies and best practices in delivering stellar value in your daily work activities.

My name is Tim Runcie and I have spent the last 20+ years supporting customers and software development companies (like Microsoft, Oracle) in building and utilizing methodologies and technology that supports Project, Program and Portfolio management.  You can say it is a passion at our company Advisicon, to helping our customers, community and practice practitioners achieve better ROI through a blend of both tools and technologies.

As a Gold PPM partner with Microsoft and a member of the Advisory Council for Project, I welcome requests from you on features to add and requests for improvement on Microsoft tools that support the Project Community.

I personally hope you enjoy this and want to encourage people to reach out directly to me for follow up, questions around this and any other technology and methodology topic.  I can be reached at Tim.Runcie@Advisicon.com

This month’s article is on Excel.  As you know, Excel is the number one tool used by project managers across the world, and is used more than all other tools combined.

Excel is an exciting, and approachable, part of the Business Intelligence (BI) report suite that Microsoft leverages for Power BI, SharePoint Reporting and for Reporting in general.

If that wasn’t enough, the Office Standard of integration means that products like MS Project have the ability to utilize the same reporting engine that Excel brings to the table.

Let’s take a look at some of the cool and great things available to you the PM community of practice practitioner.

What is New or How to Find out What is New & Coming

Since MS Office has so many integration points that work together (I like to call it the “Better Together” story), you may want to check out Microsoft’s web page for what is new in the latest version of Excel or the “evergreen” (meaning always current) version of Excel through O365.

The main page for finding information about Excel can be found here:  https://products.office.com/en-us/excel

If you purchased Excel 2016 through an Office 365 subscription (Basically Office 365), you get all of the features and will be updated automatically with new features as they are released.

Create Cool and New Chart Types

Excel has added some great new charting capabilities and chart types for their users.  I am very excited about the fact that as the IOT (Internet of Things) continues to grow, the increase of connected systems, tools and hardware creates a new market with vast opportunity for those skilled in Excel. There is more data to be analyzed, trended and reported on.

Learn more about Machine Learning capabilities http://www.sas.com/en_us/insights/analytics/machine-learning.html

For a great overview of this powerful capability https://en.wikipedia.org/wiki/Machine_learning

Imagine if you could start an MS Project schedule and then, based upon the type of template used and a few bits of information (like budget, sponsor, region) it can pull previous project lessons learned, issues, risks, and success or failure probabilities for you and load them into your new plan.  Having some great analytics and charts to show the information is a key feature of leveraging this large amount of connected or related data.

Here are some of the great new charting capabilities that Excel has enabled.

• Charting by Map
• Sunburst Charts
• Whisker Charts
• Box Charts
• Treemap Charts
• Waterfall Charts

Quick How To for New Chart Types

OK, so I’m talking Excel, but these charts are available for Excel, Outlook, PowerPoint, etc.  This functionality is based on using the “Chart type” settings found in the Office Charting Engine.

For the following examples, I have leveraged some Office graphics and information for you.

Sunburst Chart:

The sunburst chart is most effective at showing how one ring is broken into its contributing pieces, while another type of hierarchical chart, the treemap chart, is ideal for comparing relative sizes.

The sunburst chart is ideal for displaying hierarchical data. Each level of the hierarchy is represented by one ring or circle with the innermost circle as the top of the hierarchy.

A sunburst chart without any hierarchical data (one level of categories), looks similar to a doughnut chart.  However, a sunburst chart with multiple levels of categories shows how the outer rings relate to the inner rings.

To create a Sunburst Chart, do the following:

1. Select your data

2. Click Insert > Insert Hierarchy Chart > Sunburst.

3. Format colors, lines and depth as desired.

Waterfall (Bridge), Box or Whisker Charts:

A waterfall chart shows a running total as values are added or subtracted. It's useful for understanding how an initial value (for example, net income) is affected by a series of positive and negative values.

The columns are color coded so you can quickly tell positive from negative numbers. The initial and the final value columns often start on the horizontal axis, while the intermediate values are floating columns. Because of this "look", waterfall charts are also called “bridge charts.”

Box and whisker charts are most commonly used in statistical analysis. For example, you could use a box and whisker chart to compare medical trial results or teachers' test scores.

A box and whisker chart shows distribution of data into quartiles, highlighting the mean and outliers. The boxes may have lines extending vertically called “whiskers”.

These lines indicate variability outside the upper and lower quartiles, and any point outside those lines or whiskers is considered an outlier.

To create a Waterfall, Box or Whisker Chart, do the following:

1.  Select your data, either in single or multiple data series

2.  For Waterfall, click Insert > Insert Waterfall or Stock chart > Waterfall

3.  For Box or Whisker click Insert > Insert Statistic Chart > Box and Whisker

TIPS:  Use the Design and Format tabs to customize the look of your chart. If you don't see these tabs, click anywhere on the chart to add the Chart Tools to the ribbon.

If you are looking for more steps or advanced walkthroughs, definitely try the Microsoft Office Links found here:  https://support.office.com/en-us/article/Available-chart-types-in-Office-a6187218-807e-4103-9e0a-27cdb19afb90?ui=en-US&rs=en-US&ad=US

Advanced & Free Training in Excel

Of course you can’t know everything, but there are some great learning channels that you can go and utilize.  

The Office Training Center has 10 new Excel courses, created in partnership with LinkedIn Learning. Watch videos at your pace to learn about Excel 2016, including using formulas and analyzing chart data. Also, learn how to get unlimited access to over 4,000 video courses from LinkedIn Learning.

Check out this link:  https://support.office.com/en-us/article/Excel-training-9bc05390-e94c-46af-a5b3-d7c22f6990bb

In Summary

These new chart types are just part of the continual advances making Excel an excellent PM tool.  Stay tuned for more on Excel.  Powerful enhancements will continue to roll out, especially in the reporting and business intelligence space.

I hope this was helpful for you and that you enjoyed this Month’s article on Excel and some of its new capabilities.

You can find more on our YouTube Channel covering PM tools, methodologies and best practices, https://www.youtube.com/channel/UCzcAEnYWfm14KhSv4Y6H3bA, or check out our live webinars on Wednesday (called Webinar Wednesday at www.Advisicon.com/webinars ), where we present training with free PDU’s on technology supporting Project, Program and Portfolio management.  You can also find other more advanced events and activities around the globe on the events page: www.Advisicon.com/events

Again, our goal for the PMI Tech Corner is to supercharge your ability to produce results with tools, processes or a combination of both for optimization of your project management experience.

Warmest wishes for your work and do reach out to me at Tim.Runcie@Advisicon.com for questions or other techniques/tools and blended methodology approaches.  Happy PM’ing!

About the Author

Tim Runcie is the president of Advisicon (a Gold PPM Company), a 20+ year project, program and portfolio expert and member of the Microsoft Advisory council.  Tim is also the author of over 36 books on technology and project management and a 12+ year nominated and awarded MVP at Microsoft. Tim and Advisicon offer webinars, classes and customized training for all your project management needs.