Excel 2016: Charts!

Perhaps the most interesting and desirable new feature of Excel 2016 is the inclusion of several new types of Charts.  Most of us would agree that Visualization of data is key to making an impact on the users and viewers of our worksheets.  In this regard, it is widely thought that Excel was well overdue for an upgrade on its charts.

The New Charts are located at the bottom of the All Charts dialog, and can quite easily transform your data in new and wondrous ways (well, at least “wondrous” to us geeky sorts…).  There you will see the Treemap, Sunburst, Pareto (under Histogram), Box & Whisker, and Waterfall, additions.

Treemap

Likely the most interesting and question-provoking chart you’ve seen in a long while. Previously only available in Excel through VBA, this square-formatted chart quickly reveals relative sizes of your data fields.

Sunburst

This one is just plain Cool!  This newly innate chart is a visually appealing way of presenting relational datasets together in a compact form.  It can be a tough one to understand, but it sure gains your audience’s attention!

Pareto

A Pareto is a type of histogram that contains columns sorted in descending order and an adjacent line that indicates the cumulative total percentage. Pareto charts are considered one of the basic tools of Six Sigma as it facilitates seeing the most common problems or issues.

Box and Whisker

Having nothing whatsoever to do with cats, this chart is an exploratory graphic used to show the distribution of a dataset at a glance.

The New Charts in Excel 2016!  Another welcome feature to look forward to!

Excel 2016

Yes, it is that time again when we can have some Fun looking at the Latest and Greatest upcoming version of Excel.  Excel 2016 is packed with a lot of new goodies, and can be can be previewed in beta form if you wish.  Let’s take a look at just a few of the new features:

Power Query

Power Query, one of the current Go-To apps of Excel business analysts.  Previously an Add-In, it is now native-functionality in Excel.  Microsoft Power Query for Excel provides an Intuitive User experience for uncovering, combining, and refining data across a wide variety of outside sources.  Really Nice!

New Pivot Table Grouping Option

The Pivot Table is, as most of you know, one of the most powerful and simple-to-use tools in any Excel Guru’s repertoire.  A new Multiple-Result Time Grouping adds a new, quick dimension without entailing any extra work.  Pretty Cool…

Forecasting Functions

As a past business analyst, I have done a great deal of Forecasting using Excel.  Now, under the Data grouping, there is a brand new Forecast Sheet function that brings new clout to Excel’s tricks.  Good Stuff!

Slicer Multi-Select Tool

There’s a new Hammer Icon on a slicer, make slicers easier to use with a touch-screen.  Using the hammer wipes out your previous selection, and replaces it with your current choices.  Totally Rad!

And, Oh, have I mentioned Charts!  The new Chart options may be the Best New Feature Yet in Excel 2016!  We’ll talk more about that next week.  Really, really great stuff!

  

Excel 2016:  This Ain’t Yo Mama’s Spreadsheet App Anymore!

3 New Functions in Excel 2013

The advent of Excel 2013 brought with it many new or enhanced features that users have wanted for a long time.  Among those were Several Cool Functions.  Now, Excel 2013 is certainly not New anymore, but some of the functions that were first introduced in this version are worth a closer look.

ISFORMULA()

This function serves as a new informational tool. If you wish to quickly know whether a cell or range of cells contain a formula, using this function returns TRUE if the cell contains a formula and FALSE if not. Knowing the difference between data that was merely Entered and that which was Calculated, can serve as important referential purposes.

SHEETS()

You can use the Count property VBA to give you the number of sheets in the current workbook, but who wants to mess around with VBA if you don’t have to! (apologies to my very geeky friends…).  The new SHEETS() function makes it slick and easy to obtain this information without having to resort to more difficult solutions.

DAYS()

The DAYS() function returns the number of days between two dates.  Simple as that.  Now this information has previously been available by merely subtracting the StartDate from the EndDate, but this function provides the ability to combine this floating data with other functions with a more minimalistic, elegant approach.

ISFORMULA(), SHEETS(), and DAYS(), all worthy of your attention when the time is right.  Good stuff…

Duplicate Data Revisited

The proper handling of Duplicate Data has been a traditionally troublesome issue for a great many Excel users.  The Duplicates Issue can arise for several reasons:  Combining of similar databases, updating data, entry errors, etc.

While there are ways to Quickly Remove all of you duplicate data, it may be a bit rash to do so without first getting an idea of what is being obliterated.

For example, let's say you are working with Combined and Blended Sales Department data that displays your company’s reps results by individual and month.  Using Conditional Formatting, you can quickly see which records are true duplicates.

Removing Duplicates:

Since the advent of Excel 2007 (and in subsequent versions, of course), it has become laughably easy to remove your duplicate records, and to do so with a bit of Finesse:   

1.  Select the entire database (be careful not to select any self-generating key fields)
2.  Go to the Data Tools group on the Data tab, and click Remove Duplicates
3.  Select your database or table and click OK
4.  A Remove Duplicate information box will pop up (example below) giving you options as to how you want Excel to identify “Duplicates”
5.  Depending on whether you want to eliminate all duplicates by Month, Rep, and/or Sales (in this example), you will then indicate which Columns to choose with the checkboxes (Note: Choosing all of the columns assures an exact duplicate by all of your data criteria).

As with anything in life, (Excel or otherwise), once you know how to do something, you will likely wonder why it ever was a problem to begin with.  Once you know how, Removing Duplicates is a Snap!  Give it a try…

 

Wildcats, I mean Wildcards!

There are so many cool tricks in Excel.  A person could research and talk about them for years.  The use of functions is, of course, the coolest trick of all in Excel.  To make functions even better, however, you can use Wildcards.

The ability to use Wildcards is a very effective feature in Excel.  Several functions can use these handy critters to better filter the results you are attempting to get.

The following is a list of the more common Excel functions that can make use of Wildcards:

·        AVERAGEIF

      ·        AVERAGEIFS
      ·        COUNTIF
      ·        COUNTIFS
      ·        MATCH
      ·        SUMIF
      ·        SUMIFS
      ·        VLOOKUP

There are 3 different Wildcard Characters in Excel.  Though that may seem to be a paltry few, you may be Amazed at what you can do with them.

The summary below offers an explanation and examples of how these characters can be used in the functions noted above.

Wildcard Character	To
Question Mark (?)	Find any single character (letter, number, etc), such as “B?nk” finds Bank, Bonk, or Bunk
Asterisk (*)	Find any number of characters (once again, letters, numbers, etc), such as “Post*” finds Postage, Postpone, Postcard, Postulate, etc
Tilde (~) with another Character	Insert a literal question mark (?), asterisk (*), or tilde (~) such as “Jameson~?” finds Jameson?, “Jameson~*” finds Jameson*, etc

As you further your mastery of Excel formulas, keep in mind the power of Wildcards.  They can give you a great deal of help as you explore the Wild*!

Sparklines!

Sparklines are amazing little appliances that were first introduced in Excel 2010.  They are miniature charts that fit into a single cell in your worksheet, providing you with a visual representation of your data without having to resort to a full-blown chart.

Interestingly, a Sparkline is a tiny chart that resides in the background of a cell, not unlike formatting.  It can overlay (or underlay) other contents of a cell, but are typically used in a separate cell adjacent to your data.

The advantages of Sparklines are many.  They can Simplify your visual representations of your data, and can be done so with just a couple of clicks!  Effect and Simple (and that’s what we all want…).  They are particularly successful in creating Mini-Trendlines such as those which are desirable in sales analysis.  To reinforce this point, check out the illustration below.  At a glance, it is easy to tell who is doing well, and who could use improvement.

Create Sparklines

1.      Select a group of cells (or single cell) in which you want to insert your Sparklines.

2.      Find the Sparklines group on the Insert ribbon, and click the type of Sparkline that you want to create, (Line, Column, or Win/Loss).

3.      In the Data box, select and insert the range of the cells that contain the data on which you want to create your sparklines.

Badda-Bing!  Simple as that!  Want to add a bit of Illustrative Jazz to your Excel reports?  Think Sparklines!

Buying a Tesla

I am sure there are many of us who would like to own one of the new status-symbol-laden Tesla automobiles (I know I would!).  Although they do not have a built-in Excel application, the all-electric, futuristic Teslas come with a host of high-tech features.

So, how do you know if this is a car you can afford?  Well, if you have your tablet or a laptop along with you while you are shopping, you can calculate the Monthly Payments using a handy-dandy Excel function.

The PMT() Function - Here is How it works:

Coming up with this information in Excel could hardly be easier.  The PMT() function can calculate the monthly repayment using the following syntax.  Note that a minus sign has been put in front of the PMT function so that it returns a positive payment amount (which is more intuitive for most folks…), and since we are looking for Monthly Payments, the first argument is divided by 12.  The rest is a piece of cake:

-PMT(AnnualInterestRate/12, NumberOfPayments, AmountOfLoan)

To illustrate this, let’s say you traded in your Jaguar (we might as well dream…), and your bottom-line amount of your loan is going to be $51,000.  If your annual interest rate is 3.5%, and you are financing for 60 months, the resulting formula would look like the following:

=-PMT(3.5%/12, 60, $51,000)

Having constructed your formula with very little effort, you hit Enter and, Bamm, you have your monthly payment (In this case, $927.78).   

Too much money?  Don’t have a Jag to trade in?  If you don’t like the results, play around with the interest rate, the length of the loan, or maybe you’d want to check out a Chevy Volt.  The bottom line is that it is all so simple when you use Excel.  Coolness…