6 New Chart Types in Excel 2016

Before the year 2016 begins, Microsoft has already unveiled Microsoft Office 2016 suite – with a number of enhancements, features, and completely new things that extend the existing Excel and takes it to new levels.

In the latest and greatest Microsoft Excel 2016, we see 6 new types of charts, which will help to transform the data into much better insights, information and visualization delight than ever before.

Microsoft Excel 2016 boasts New Charts that can display:

  1. Sunburst Chart
  2. Treemap Chart
  3. Waterfall Chart
  4. Stock Chart
  5. Pareto Chart
  6. Box and Whisker Chart

shows the new charts in PowerPoint2016

The Sunburst chart looks like a pie chart, but has rich, extended functionality. You can now visualize the data at multiple levels, which was simply not possible with a pie chart.

Business Analytics in Excel 6

The Waterfall chart in Excel is a welcome addition. Previously, we had to write cumbersome VBA code, and even use external charting applications to create waterfall charts. This type of waterfall chart is great to show stock price movements.

Business Analytics in Excel 7

A Pareto chart shows the 80-20 Rule, which applies to any business, in any industry, and has been proven to be a great indicator of the top KPIs that make the difference. Doing a 80-20 Pareto Analysis required us to Build a 2 Axis chart in previous version of Excel (like Excel 2013, Excel 2010 or Excel 2007 etc.)

Excel 2016 Software: If you do not have the Microsoft Excel 2016 software yet, you can easily get it online here.

Want to Learn Excel 2016: There are several books on Excel 2016 already available, and you can also join the ExcelChamp Academy for online video based training.



These new chart types in Excel 2016  will help us in creating beautiful charts in Excel, and take it to the next level of visualization of data, and presentation for our clients, management, users, and for our own data analysis and charting analysis.

In the coming weeks, I will be highlighting more new features of Microsoft Excel 2016. Do let me know if I can help you in any way in using Microsoft Excel 2016.

Cheers,
Vinai Prakash

 

Be Sociable, Share!

    7 Habits of Highly Effective Data Analysts

    Converting Data Into Information Using Excel
    Converting Data Into Information

    We all are sitting on mountains of data, and new data arrives each day in the form of Reports, CSV files, Charts from Marketing, Logistics, Sales, Websites, Google Analytics… Before you can make any sense of it, even more data will arrive.

    Today we have much greater processing power in each computer than 10 years ago, yet we are not making appropriate use of it to process the data and create information.

    I am sharing some of the best techniques used by data warriors & power business analysts. These are not really secrets… but best practices, that aid in converting data into actionable information.

    1.    Clarity of Objectives: Before you begin your gold mining, define some broad goals or identify some of the problems faced by you or your company.

    Is it low sales, low margin, low traffic or high CPC?

    Once you have clarity on what exactly you are trying to analyze, you can begin our data analysis.

    2.    Clean the Data: Most raw data arrives in a pretty bad shape. You need to remove duplicates, fill in some missing blanks or values, and get dates in a uniform format. This will make the later steps easier… or else it will be garbage in & garbage out. To check if the data looks good, try to sort it on different criteria, and have a look around. If it looks clean and complete, then you can begin the next steps in data analysis.

    3.    Spot the Trends: It is easier to identify some trends in the data, and then analyze them further. There are several ways to spot the trends quickly. Some common methods are to visualize your data with the 80-20 rule. Identify which 20% of the factors contribute 80% of the results. Create bar charts, sort in descending order, and create a cumulative frequency chart with both axis to generate a quick Pareto chart displaying the 80-20 rule.

    Another excellent way is to generate measures of central tendency – using Mean, Median, Mode, Outliers, Range, Variability and Skewness of data. They tell quite a lot about your data pretty easily, and make it easier to spot trends within the data.

    Converting Data Into Information With Excel
    Converting Data Into Information With Excel

    4.    Set up KPIs: Create a set of common Key Performance Indicators (KPI) for your line of business/company, so that everyone using the KPI will have a common understanding. Right KPIs shed light of performance and makes it easier to understand areas of improvement.

    Some of the common KPIs you could set are ROI, EBITDA, Net Profit Margin, Customer Lifetime Value, Market Share, Brand Equity, Cost per Lead, Customer Turnover Rate, Earned Value, Quality Index, Carbon footprint, or Supply Chain Miles.

    With KPIs, and their trend, you can then find the story told by the data. Identify the reasons and take appropriate actions. Tracking KPIs over a long time period makes it easier to spot trends in seasonality, sales patterns, demand surge and profitability across months and quarters.

    5.    Common Repository for Data: Set Up a common data repository, from which everyone draws data. It is quite common in larger companies to have multiple islands of data. Everyone seem to have a ghost server under their desk, compiling data from different sources and reporting off it. Thus, different stories are told in the board room, and the management often wonders which version is really the truth?

    A common source brings more sanity, and trust on the data and reporting. A common data warehouse from where all management reports are generated is a great idea.

    6.    Visualize Using Charts, Graphs & Dashboards: A picture is worth a thousand words. Rather than creating voluminous reports full of numbers, display the summarized information in the form of line charts, bar charts, spark lines and various other chart types. What may not be visible in data may jump out at you visually, in a chart. It is much easier to find actionable insights in charts. Fortunately, most data analysis tools come with excellent charting capabilities.

    Create Simplified Reports Using Dashboards. Multiple summarized reports and charts can be compiled into a management dashboard. With key KPIs, charts and data visible on a single piece of paper or screen, it becomes much easier for senior management to make quick decisions.

    Dashboards are dynamic, making it easier to compare month on month, quarter or quarter, division to division performance and spot trends quickly.

    These visual implementation must be idiot proof – so simple that a O level student should be able to interpret it pretty easily.

    Use simple tools for the analysis. It is not necessary that the next shiny reporting tool or expensive BI tools will make it a breeze. It takes many months of painstaking work to get to a standardized dashboard. A visually appealing and simplified dashboard makes analysis and reporting fun, something to look forward to.

    7.    Constant And Never Ending Improvement (CANI): Experienced analysts are always on the lookout of opportunities to further extend their analysis, improve their dashboards and identify new insights. Ask your clients and users how they use the reports and dashboards, and seek ways to improve it.  Be open minded, flexible, inquisitive and persistent in your pursuit of information excellence. Ogle at your data from different angles and different perspectives. It will enable you to discover new insights and add value to your business.

    Implementing these best practices will enhance your data analysis experience, and will enable you to create value for your clients, bosses, and with the new insights found, you can improve your business performance, productivity, and profits!

    Cheers,
    Vinai Prakash

    About The Author: This article has been written by Excel expert Vinai Prakash. Vinai has over 28 years of experience in business intelligence, data mining, and creating useful management dashboards and reports.

    Vinai runs his own training company Intellisoft Training, and has coached over 5,000 executives and management on creating dynamic dashboards using Microsoft Excel. Vinai runs his blog on Excel Tips & Techniques at http://www.ExcelChamp.Net

    Vinai Prakash will be conducting a 2 day Excel Dashboard MasterClass in Singapore in December this year. Contact http://www.intellisoft.com.sg or call +65-6296-2995 for more information.

    Be Sociable, Share!

      How To Make a Dropdown List in Microsoft Excel

      You have seen online application forms where the Country Names are selected from a drop-down box. Or to select the Name of an Employee, or Cost Center Codes, or Departments etc.?

      Drop Down list of Countries in Excel
      This kind of list serves 2 purposes:

      1. It makes it easy for the user to select a value, rather than type it.

      2. It makes the data consistent. No garbage values come in. The user can only select from the list of values provided.

      Well, it is extremely easy to make a drop down list like this in Microsoft Excel. Here’s how you do it, step by step. And this will work in any version of Excel – Excel 2003 , Excel 2007, Excel 2010 and Excel 2013.

      1. First of all, make a list of all the country names you want to display in your drop down list.

      2. Select all the country names, and then go to Formula tab > Name Manager, and click New button.

      3. Type a name like countries for the range. You must make sure you write it as one word. Range names can not have blanks, spaces or special characters, except for the Underscore.

      data_validation_list
      4. Now go to the cell where you want to place the dropdown box. So let’s say you go to cell A1. Click and stay in Cell A1. Then click the Data tab > Data Validation.

      data_Validation
      5. Pick List from the Allow: dropdown box. See screenshot above.

      6. Key in the Source as =countries. This must be the name of the range you just created in Step 3. Now click OK to close this dialog box.

      7. Once this is done, you will see a drop down arrow showing up in the cell C1. You will see the list of countries showing up here.

      drop_down_filled_valuesOnce you have selected a value, it will be displayed in cell C1. No mis-spelt values. No garbage. Pure, good, data validation at its best!

      You could have your master list of countries on another sheet in the same workbook, or in the same worksheet. If you do not want your users to see the individual names, you could hide the sheet and even protect it from any changes. But these things will be covered in a separate lesson.

      Hope this helps. Do post a comment if you enjoy this little tip!

      Cheers,
      Vinai

      Be Sociable, Share!

        Showing The Hidden First Column in Excel

        It is quite easy to show or hide columns in Excel. Simply select a column, and press Control + 0 . The column is hidden from the view. You can make it out because of a dark line separating the columns. If you hide the B column, you will only see column A & Column C, and it is apparent that column B is hidden.

        To unhide, simply select both column A & Column C. Then right click and select Unhide. Column B is brought back into view.

        This works great most of the time. And it works in showing or hiding rows too.

        But the problem arises when you want to hide the first column – Column A. Now it is difficult to select 2 adjacent columns, and you are unable to Unhide Column A.

        Steps to Display the Hidden First Column (Column A) in Excel

        1. Press F5. The Go To dialog box will popup.
        2. Key in the cell A1 in Reference, and press Enter.
        3. The cursor would have moved to the cell A1, even if you can not see it. Do not worry.
        4. Now go to the Home Tab (in Excel 2007, 10 & 2013)
        5. Click on Format button – it is near the far end of the screen… toward the right side of the ribbon.
        6. Choose Visibility > Hide & Unhide. Then select to Unhide Columns.

          Unhide columns or Rows in Excel
          Unhide columns or Rows in Excel
        7. Voila! You will now be able to see the column A. It has been un-hidden.

        It is a simple trick. Excel is all about simple tips and tricks… The more you practice, the more you try, the more gold shall ye find!

        Cheers,
        Vinai Prakash

        Be Sociable, Share!

          Displaying Large Numbers in K (thousands) or M (millions) in Excel

          For my small Training business, I haven’t reached the revenue target of Million Dollar Sales… But yes, I do achieve sales of several thousand from my blogs each month (PMChamp, ExcelChamp, PhotographyChamp) and my Training business in Singapore & Excel Dashboard Consulting around the world.

          How to Show Large numbers in K or M within Excel
          Large Numbers showing in K or M in Excel

          However, my clients are usually SME and MNCs, whose revenue us usually in Millions and sometimes in Billions too 🙂

          Difficult to Read, Large Numbers
          The problem is that for some people, it becomes difficult to read numbers and figures in Thousands, Millions and Billions, with so many zeroes to count.

          And for our neighboring country Indonesia, the currency denomination is so small, that a rent of a one room apartment in Jakarta may be anywhere from 5 million to 10 million Rupiah. So you can easily imagine how doing a simple budgeting exercise can take you into dizzying heights of billions and trillions.

          Adding a Thousand or Million Suffix to Numbers in Excel
          For huge numbers, it is easier to read $23M or $25K rather than  $23,000,000  or $23,000. So let’s see how you can convert a large number in Thousands, Millions or Billions to a easy to read number.

          1. Simply select the number cell, or a range of numbers that you would like to simplify.
          2. Right Click, and choose Custom Formatting.
            You can also choose Number Formatting from the Home Ribbon, or simply press the shortcut  [Ctrl] + 1.
          3. Go to Custom, and key in 0, “K” in the place where it says General, and close the popup.Custom Number Format Popup in Microsoft ExcelCustom Number Format Popup in Microsoft Excel 
          4.  Voila! Now your large number will be displayed in Thousands.
          5.  And if you want to show the numbers in Millions, simply change the format to 0,,”M”.  The figures will now be 23M.
          6. If you would like to see 23.6M, you can format it to 0.0,,”M”
          7. What’s great is that if you now create a chart on this data, the chart or Pivot Table will now show the figure in this custom format. Thus you will see the chart showing in Millions, or Thousands… saving space, and making the chart or pivot easier to read and analyze.

          That’s it! Spend more time analyzing, not staring at the numbers 😉

          You can check this out for yourself, by downloading the Sample Excel File.

          Download: Number_formatting_in_K_or_M.xlsx

          This technique applies to Excel 2003, Excel 2007, Excel 2010 & Excel 2013. This would work in all versions of Microsoft Excel.

          Hope it helps!

          Cheers
          Vinai Prakash

          Be Sociable, Share!

            Solving Everyday Problems in Excel [Survey]

            Each week I receive questions from people facing issues with Excel. Some need help on Excel Formulas, Pivot Tables or Macros.

            What issues are you facing in Excel?

            Solving Everyday Problems in Excel [Survey]

            Each week I receive questions from people facing issues with Excel. Some need help on Excel Formulas, Pivot Tables or Macros. What issues are you facing in Excel? [wwm_survey id="0"] Thanks. This will help me to create more useful Excel Tips and Tricks, that will address the issues you face with Excel. Cheers, Vinai Prakash

            Thanks. This will help me to create more useful Excel Tips and Tricks, that will address the issues you face with Excel.

            Cheers,
            Vinai Prakash

            Be Sociable, Share!

              Extracting Text From Variable Length Strings

              After I wrote about how to extract characters from the left, right or middle of a text string in Excel, I received a few inquiries about extracting text from strings which don’t seem to have a fixed size, and vary in length.

              For example, if the data is something like this

              HQ-1022-PORT
              LONDON-4053-LANDED
              HOUSTON-2488-WEST
              SINGAPORE-3133-LEEDON

              You want to separate out the first word (HQ, LONDON, HOUSTON, SINGAPORE etc.)

              Can’t Use LEFT Function
              You can’t use a LEFT function, because a left function needs to know the number of characters you want to extract. And this itself is variable… There are 2 characters in HQ, 6 in London, 7 in Houston etc.

              One of the ways I have discovered is to find the location of the first Hyphen (-), and extract all characters from the left of it.

              Finding the location of the First Hyphen
              We can use the FIND function to find the location of the first hyphen.

              If A2 contains HQ-1022-PORT, we can use the formula as:

              =FIND(“-“,A2)

              The answer would be 3. This means that the hyphen is the third character in the string. This is perfect. Now we know that we need n-1, that means 3-1, which is 2. We need 2 characters from the Left of this number.

              So we can write another formula as

              =LEFT(A2,  FIND(“-“,A2)-1 )

              The resulting answer would be HQ. Copy the formula down to other cells, and you should be able to extract HOUSTON, SINGAPORE etc. without much hassle, and without having to write another, different formula.

              One size fits all formula to extract a variable length string from a piece of text. And pretty easily too!

              Do you have any other method? How would you solve this challenge?

              Please post your answers in the comments below.

              Cheers,
              Vinai Prakash

              P.S. – I am teaching a 2 day course on Data Interpretation & Analysis in Singapore on Feb 9-10, 2015, and another on March 9-10, 2015. If you would like to learn techniques to analyze data and create management reports, you can attend this program.

              Awaiting you and your innovative comments below … 🙂 – Vinai

              Be Sociable, Share!
                Skip to toolbar