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.)

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


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’s Online Training for the Pivot Table MasterClass, available on our ExcelChamp website. A few, short videos will teach you the master techniques that are used to play with Pivot Tables, and generate powerful reports from Excel Data using Pivot tables. This video training is recorded and provided directly by me, Vinai Prakash, at the ExcelChamp Website.

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.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!


Cheers,
Vinai Prakash
Founder: ExcelChamp.Net – Simple Tips to Get More out of every day Excel, and be an ExcelChamp!

Creating Beautiful Excel Charts For Business Presentations

Microsoft Excel is used to produce charts that are used in the boardroom, in business presentation, and in school projects all over the world. However, most people do not go beyond the standard charts that Excel produces by default, which could be the most boring, and most unsuitable of charts.

In this article, I am going to show you how you can add value to the default Excel chart, and make it really sing, with just a few clicks. Plus a few tips on how to put some glamor, some jazz, some oomph to your charts to make them beautiful, sexy and cool!Excel Default Chart... Not the most exciting chart.

Remove Chart Junk: The default Excel chart has a lot of junk. See the before and after picture below.excel_chart_data

Now let’s see how to arrive at this result ourselves. If you would like to try it yourself, see the image above for the Excel data file with the original data showing. You can key in this data to get started yourself.

improved_excel_chart

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


1. Move Legend to the Bottom: The default Legend on the right side of the chart steals away valuable space from the chart. So move it to the bottom. Simply Go to Chart Layout Settings, and Click Legend, then select Bottom. The legend moves to the bottom, and the chart bars now have more space to expand.

2. Remove Grid Lines: Sometimes the grid lines do not add much value, and are best eliminated, as they just make the graph look cluttered.

3. Show Data Values: You can show data values on top of the bars, inside the bars, or at the bottom. Once you have the data bars showing, you really do not need the grid lines anyway.

4. Remove the Vertical Axis: If the values of the data bars, and the Title of the chart are self explanatory, then you can get rid of the vertical axis, giving you more room.

5. Add a Chart Title, Above the Chart: Choose the Title from the Layout, and then select “Above Chart”. This way, your data will never hit the title.

6. Change the Theme: Most people live the default Office theme. But Excel provides over 50 different themes, which can make your charts look much sharper, out of the box.

7. Apply Custom Colours: If the theme colours do not suit you, you can pick any colour you like for any data bar or pie.

8. Add a Company Logo: You can add an image anywhere in the chart. It could be your company logo, or any particular product. Do not add clipart for the sake of adding it. It looks quite cheap and amateurish.

With these simple tips, you can improve your charts and graphs, and give them a new lease of life… make ’em jazzy, snappy, and cool!

Cheers,
Vinai Prakash

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Top 10 Time Saving Tips in Using Microsoft Excel

If you are a data warrior, chances are that you use Excel extensively in your day to day work. Learn to Analyze Data Quickly With Excel.

And there are many ways to boost productivity within Excel. Use these nifty tricks and improve productivity.

1. View All Formulas in Excel With a Single Click: I wrote about this trick some time back. It allows you to view all the formula with a single click of Control + ` Key. This acts like a toggle key. This can save a lot of time in finding where are the formulas to edit/view them.

2. Clean up Your Data  – Identify the Duplicates, and Remove Duplicates: There is no point in having duplicates in your tables. There are multiple ways to do this. You can identify the duplicates, and then choose to remove them manually,  or you can request Excel to remove the duplicate rows completely, automatically.

3. Colour Alternate Rows and Make it Easier to Read Large Data Sets: Prior to Excel 2007, we used to write formulas to colour alternate rows in Excel. This makes it easier to read the data.

=MOD(ROW(),2)=0

This function would tell us if the row is an even row or an odd row, and then we could colour it.

But since Excel 2007, we can do this in multiple ways. We can convert the data into a table. And in the table options, you can then colour alternate rows or columns as bands.

And then you can also use the formula above, and use conditional formatting.

4. Learn The In-Built Functions in Excel: Many people are amazed at the plethora of in-built functions available in Excel. But you must know that they exist, and also know the syntax to make a good use of them.

5. Learn and Use Pivot Tables Effectively: Pivot tables summarize data quickly. Learn to use them effectively.  Simple techniques like displaying both values and percentages in a Pivot Table adds extra value.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Similarly, learn to show different grouping for different pivot tables, even though it is based on the same data source. Also, creating a calculated field in a pivot table adds extra value.

If you are new to pivot tables, this introduction on how to analyze data using Excel pivots will be useful.

6. Charting Techniques Can Make People Notice Your Work: Creating nifty charts like the 2 Axis chart, or the PivotChart can be useful in the board room. Learn these techniques. Similarly, using Slicers in Pivot Charts can make dynamic Charts that can wow the audience. They are simple to use.

7. Get Back to Your Selection: Many times you will select or highlight some cells, but then move about the Excel file to find something. Then your are lost. Now you don’t remember where exactly is your selection. Simply click Control + Backspace. It will take you to the highlighted area in a blink!

8. Use Range Names to Refer to Cells: Instead of using G3, C3 or F3, you can give each cell a name. Not only single cells, you can actually define a name for a group of cells too. Then you can refer this group of cells by a particular name.

So instead of saying =G3+C3-F3, you can now calculate Salary as

=Basic_Pay + Allowance – Tax

This is much easier to read, and comprehend. No need to look up where the formulas are coming from, as the names are self evident.

9. Learn Excel Shortcuts: There are hundreds of shortcut combinations in Excel. It is impossible to remember all of them. But you must know a few to really speed up your work. Some of my favorites are:

  • Control N: New workbook
  • Control `: View All Formulas
  • Control Backspace: Jump to selected Cells
  • Alt + F1: Creates a inserts a chart based on the currently selected data as an embedded chart object.
  • Alt + =: Auto sum formula is inserted
  • Ctrl + Shift + Enter: Enter an Array Formula
  • F9: Calculates all the worksheets in all open workbooks

10. Learn to Use Dates Effectively: In Excel, dates are stored as numbers. They can be displayed in a wide variety of Formats. Understand these formats to display them in the way that suits the applications.

Also, you can calculate using dates, for example, you can find the difference between any two dates. You can add a fixed number of days to a date.

Hope you enjoyed these tips, and will use them to improve your productivity in using Excel. Do post a comment below to talk about your favorite tips in Excel.

If you wish, you can get a good book on Excel from the Excel BookStore. These books are usually quite inexpensive, and will give you many ideas to improve too.

Cheers,
Vinai Prakash, PMP, MBA, ITIL, GAP, Six Sigma

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

P.S.  : You can contact us to conduct a workshop on Excel Tips and Tricks at your office.

And I hope you subscribed to the ExcelChamp Excel Tips Newsletter already. 🙂

Additional Resources for Learning Excel & Become A Pro

  1. Top 12 Most Important Excel Functions You Absolutely Must Know
  2. Excel Beginners Guide
  3. Excel Essentials: From Beginner To Pro in Just 6 Weeks

Dynamic Chart Titles in Excel

A chart title in Excel is  usually hard coded.

This means that when underlying data changes, the heading does not change… it remains static… unless someone notices it, and modifies it manually.

Thus, headings like “Sales for September 2014”, “Revenue for Q1, 2014” etc. are not wise, and may even reflect the wrong picture… specially if the data is updated, but the heading is not.

Most people do not realize that it is possible to customize the heading, and it could be a formula, or based on any cell in the worksheet.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


How To Customize A Chart Heading?

Simple. First use formulas to build a dynamic heading in a particular cell in Excel. It could be like:

=”Sales for ” & A1

Second, Click on the Heading in the chart. Once the heading is highlighted, move to the formula bar. Either key in this formula in the formula bar, or refer to the cell which contains this formula.

Now the chart will show a Dynamic Chart Heading… that changes, based on the data selected.

Hope this helps! Let me know if you have any other challenges in Excel.

Cheers,Vinai Prakash

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Do You Use These Features of Microsoft Excel?

Most people hardly use the most useful features available in Microsoft Excel. Many are just using Excel as a calculator to do basic calculations like adding or subtracting numbers. This is a gross under use of Excel’s vast potential and feature rich functionality.

Do a quick check, and see if you use these advanced features of Microsoft Excel in your day to day work to improve your productivity and efficiency.

  1. Finding the Top 10 Customers or Finding the Bottom 5 Performers in the organization
  2. Give Names to Range of Cells, and then use them in formulas for easy referencing and decoding – like =Salary – CPF + Allowance
  3. Exploit Pivot Tables to Summarize the data and slice & dice it in any way – like finding sales by product groups, or calculating productivity by department, or calculating sales by department by product by area.
  4. Write Macros to automate routine things that save you a huge amount of time – example creating different pivots, charts, tables, and doing complex calculations, or compiling the cost benefit ration, cash flow statement automatically.
  5. Highlight values that are above or below a certain threshold – like all sales above $25,000 to be highlighted, or all products with a net profit margin of less than 5% be highlighted.
  6. Sort the values in Ascending, Descending or any Customized sorting order – like sorting in order of Manufacturing, Accounts, Sales departments.
  7. Use advanced filtering conditions, and be able to filter data using multiple different criteria – like salary > 50,000 and sales <100,000 or Zone in East, West.
  8. Create fantastic charts that portray the given business situation perfectly. There are over 50 different types of charts to choose from, and each has its edge, advantages and a reason. Do you know when to use which type of chart – like two axis charts, stacked column and line charts, bubble charts, pie, slice of a pie etc.
  9. Create management dashboard that are dynamic, and provide a complete snapshot of the key business KPIs in the company – change the chart values at the click of a checkbox or change in a dropdown value
  10. Use Excel’s advanced What-If analysis to do projections for future, forecasting, trend analysis etc. with ease
  11. Use Lookup tables to find any value or corresponding value from a table using advanced functions and formulas – find values that in the left of the looked up value
  12. Protect certain cells from data entry, and hide the formulas from other cells. Ensure that only certain values are keyed in the cells to avoid data discrepancies.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


This is just the tip of the iceberg… These are common things that can be done easily with Excel’s in-built features.

So how many of these did you know? And how many would you like to learn more about? Even if you learn one new trick in Excel, it will save you immense time and improve your productivity. Investing in Microsoft Excel Training bears fruit in no time, and is a no brainer.

Microsoft Excel is really extremely powerful. Each version of Microsoft Excel – be it Excel 2007, or Excel 2010 or Excel 2013, Excel 2016 or Office 365 adds more and more features to the already powerful dynamite of a package.

At ExcelChamp, we teach people how to leverage the maximum power out of Microsoft Excel in short training courses.

So what are you waiting for? If you would like to learn any one or more of such useful features of Microsoft Excel, just let us know. We provide online training through videos, which will show you each click, step by step, so you can master the new techniques easily, with exercises, and practical examples.

Go ahead, equip your team with the right skills. Get everyone on board to learn the basic and advanced features of Microsoft Excel, and Be Awesome in Excel!

Email to us to know more about Online Excel Training at ExcelChamp.Net.

Cheers,
VInai Prakash, PMP, ITIL, GAP, Six Sigma, Excel Guru

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!