If you’re a person with heavy data analysis needs – for your home or for your business, you’re likely to be awfully pleased with the new Excel Workbook features, introduced by Microsoft with the 2016 release. Excel commands a staggeringly large loyalty base, with over 750 million users across the globe, each with their own diverse needs and vociferous inputs on what can make this tool better. Well, if you consider the past few releases, Microsoft has definitely been listening!

The new 2016 Excel Workbook  features, make quite a difference in your analytical computing usage of the tool. From simple, to complex, to brilliantly profound, Microsoft has packed it all in, making the 2016 Excel Workbook so much more versatile, smarter and mightier. In this post, we celebrate the recent (2016) changes that transform the humble Excel Workbook into its present potent form.

1. Smarter Autocorrect

Think of the large number of times you needed an Excel workbook function or formula, but you’ve forgotten the exact name. You know it exists but are currently clueless on the right fit. For instance, let’s say you want to check if a particular cell contains numeric values. So you know that the function to use has something to do with (but obviously) “number”. And you type “number,” hoping Excel will provide the rest. It doesn’t.

So you ask your friend seated in the next cube, and he asks his friend (err, Google), and finds out that the function you’re looking for is called “isnumber”. Bah! Yes, it sounds fairly simple to remember. But life would have been infinitely easier if Excel had been more helpful.

Guess what folks: Microsoft has noted your pain!

Now, the autocorrect feature has smartened to include not just the specific text you type, but all formulas and functions that include your text. So you no longer have to memorize the exact names, casing and all, coz your Excel workbook’s inbuilt autocorrect feature smartly supplies it all. This is a big relief; thank you Microsoft!

2. Improved Functions of Excel Workbook

Following on its vast improvement on the smarter autocorrect feature, Microsoft takes this reasoning into other Excel Workbook functions. There are too many to name here, but we’re listing a few of the more popular ones for your reference.

• IFS: Consider that you’re trying to compute a fairly simple calculation through Excel – If a particular cell is a number, an odd number, and if its sum with the previous value is greater than X, set value to Y. That’s just about 2-3 ifs for you. But by the time you capture this in Excel with all that nested If, it gets complicated! The new IFS function works much like normal brackets do. No nesting required; instead, simply list your calculation in order from left to right, and Excel will figure out the obvious. Oh, and it also comes with an else “catch all” that comes into effect when none of the IFS are met. Nice!

• CONCAT: This is an extended version of CONCATENATE, with a simpler name. It’s easier to type, and also supports a range of cells references. (The older concatenate function is also available, but only supports single cell references).

• MAXIFS & MINIFS: This uses the IFS function to capture multiple criteria across a range of cells, and then find the maximum/ minimum amongst the cells that meet the specified criteria.

• TETJOIN: With this function, you can combine text from multiple cells across several ranges, and list them together separated by delimiters. Mighty useful when you want to convert a bunch of rows/ columns to text, based on additional criteria.

• SWITCH: You can consider this an advanced extension of the IFS function, where an expression is checked against multiple values, and the first “match” is returned. If none of the values match, an else clause holds true.

3. New Funnel charts

Do you know how you often wish the bar graphs in your Excel workbook could align in order, so they show progression? Or heck, even regression, so you can demonstrate how a task has been achieved over time, in phases. The new funnel Chart type facilitate this.

Funnel charts are among the newer lot of charts introduced in 2016. Unlike bar charts that are all over the place reflecting statistical data, funnel charts can be utilized to capture the gradual completion of a process/phase/stage, over time. For ideal use, the numbers should decrease so the bar graphs line up in a neat funnel.

For example, let us consider that you want to capture the different phases of an Engineering/Construction project, to illustrate the total workload complete at each stage. A funnel chart would be your natural preference, as showcased in the figure below.

4. Smarter insertion of pictures

This might seem like a trivial feature in a computing application like Excel. Yet, it has worth. Insert a skewed picture, and Excel will automatically rotate it right to suit the camera’s orientation, so your pictures display straight and perfect. With this, Microsoft is becoming increasingly picky about finish. And that, is a good thing!

5. Introduction of 6 New Chart Types

We’ve already covered the Funnel chart type earlier. But other new additions include:

• TreeMap chart: This is a hierarchical chart type that cleverly allows for blank cells within its structure. For instance, let’s say that you want to capture the different summer courses offered in a University on weekdays and weekends. Some days have 2 courses, some have 3, and some have only one course offered. This is difficult to capture using any other chart type, as the distribution is not even. But with TreeMap charts, you can compare courses easily, and use colors to classify them differently.

• Sunburst Chart: Consider the older Doughnut chart – it captures data, without hierarchy. This has been extended to include hierarchies and blank cells, making up the new Sunburst Chart. This chart too supports classifying multiple categories through color.

• Histogram Chart: This is best used to capture distribution data and multiple frequencies within each distribution (called a “Bin”). This comes in 2 types, the straightforward Histogram, and the Pareto chart (Histogram sorted in descending order of chart lines).

• Box & Whisker Chart: This chart type is useful to capture more complex statistical data.

• Waterfall Chart: This chart type is best used when you want to capture the running effect on a single attribute, over stages. For instance, let’s consider the financial figures through 4 quarters. In each quarter, this may be positively or negatively influenced by other attributes (like cost of sales promos, salary payouts, cost of raw material, etc.). The waterfall effect on the financial results can be easily illustrated using this chart type.

6. Solid Database Integration, providing better BI.

Perhaps one feature sorely lacking in earlier versions of Excel Workbook was database compatibility and integration. While the world was all about big data, Excel provided but the most basic database functionality, and any advanced feature was at best an add-on feature. This is fixed in the 2016 version.

The new 2016 Excel Workbook comes pre-loaded with advanced DB features, easily accessible through the Data Tab. This includes:

• Fetching data from external Database through a “Power Query” (accessible via Data Tab->New Query->From Database)

• Creating a relevant data Model for tables imported from different Databases.

• Retention of Power Pivot, first introduced in the 2013 version. This was Microsoft’s biggest improvement in the BI space. The Pivot Table too comes with multiple enhancements, including:
  • Automatic grouping of time-related fields.
  • Automatic detection of relationship between tables.
  • Drill-down buttons so you can easily analyze data at the lowest level of hierarchy.
  • Refined creating, editing and deletion features to better manage each Chart.

• PowerViews and PowerMap, earlier add-on features (in the 2013 version) are now part of the main application. These can be enabled through File->Options->Advanced->Data->Enable Data Analysis Add-ins: Power Pivot, Power View & Power Map. (Note: The Power Map feature is now called 3D Maps, and is directly accessible via Insert Tab->3d Maps)

• Enhanced forecasting with a single mouse click: Earlier versions supported only linear forecasting of data, not so with the newer version. The 2016 Excel workbook comes with an exclusive forecasting button providing powerful “one-click forecasting” features. This is accessible via Data Tab->Forecast Sheet (within Forecast Group). The subsequent “Create Forecast Worksheet” wizard empowers you to effortlessly create a visual forecast based on “exponential smoothing”, a statistical model far superior than mere linear forecasting.

Together, these 2016 Excel Workbook features make it a BI-intelligence tool to reckon with!

7. Templates

Excel 2016 comes additionally loaded with 3 interesting templates that support your varied personal data-analysis needs.

• My Cashflow template: This empowers you to play around with your money. Err, virtually. This template helps you import your expenses, so you understand how your money is being spent.

• Stock analysis template: As the name indicates, you can import data related to your (NASDAQ) stocks and monitor them from Excel.

• Calendar Insights: This one helps you import your calendar information into a data dashboard, so you recognize how your time is being spent.

As you can see, each template provides potential features that are a far-leap from the simple Excel Workbook. Once chosen, these templates quickly create the initial set-up for you, so all you have to do is import raw data (from Outlook, or an external Database, or a compatible data source) and watch the complex data analysis/ forecasting/ charts unfold!

8. Ink your Equations

This one is for those who think far better with a pen (or a stylus), than a mouse/keyboard. Have a complex math equation you want to use in your 2016 Excel Workbook? No problem. Just write it by hand using a touch device, and Excel will do the rest. This feature of converting (almost) hand-written equations into Excel-readable text is accessible via Insert Tab->Equation->Ink Equation. A preview screen allows you to insert the equation using a stylus or your finger on a touch-sensitive device.

In addition to the above features exclusive to 2016 Excel Workbook, there are also other refined features that run through all Office 2016 applications, including Excel.

• Students from registered Schools/ Universities can access the online version of 2016 Excel Workbook completely free, as part of the (online) Office 365 Education suite. Incidentally, this suite also comes with 1TB data storage availability on OneDrive, making it easy for users to back their 2016 Excel Workbook data within the cloud space.

• Office 2016 is power packed with features that make online collaboration with other Office 2016 users seamless. The “Share” feature facilities this across the Office Suite. Excel additionally empowers you to share your data analysis results (like reports, charts and the whole Workbook itself) with other users within your workgroup using the “Publish to Power BI” feature.

• Do anything more easily with “Tell Me”. This is Clippie, the older Office helper tool’s descendant, except that it comes as a straightforward button. The pop-up window allows you to insert your request (like “How to create a pivot chart”), and “Tell Me” will guide you through the detailed steps to complete your request.

• 2016 Excel Workbook provides constant access to a Bing-powered lookup called “Insights,” which helps you stay on top of what you’re working. For instance, typing “Financial forecast” will bring up all related pages and online articles in no time. It’s like having the world-wide-web embedded into your Excel Workbook!

• Improved readability with 3 new themes: Color, Dark Grey and Dark. These use high-contrast color palettes without being an eyesore.

In summary, we hope that this post has clearly illustrated the finer features of the 2016 Excel Workbook. If you’re on the fence on trying/ buying this latest product, consider its top 3 advantages:

• More powerful Database integration features.
• More powerful BI and forecasting features.
• Solid templates that can help you track and manage your money, stocks and time wisely.

These three features are its best “selling points”. We reckon that it’s more than worth it!

The post 8 Awesome & Useful Features of 2016 Excel Workbook appeared first on eduCBA.