Many of you are familiar with Excel. However, Microsoft has introduced some new features in their 2016 version you may be missing out on.


New Templates

If you’ve been using Excel for a long time, you probably skip over the templates.  But, you might want to take a look at these new ones.  These new templates will make your presentation look professional. You can modify the templates if you wish.  It’s much easier to do this than to create a new one.

Whenever you start a new project, you must create a new workbook. You have the choice of creating a blank workbook, or use one of Microsoft’s great pre-designed templates, such as My Cashflow. This template is useful for managing how your business earns and spends money. This template actually contains data—you simply need to enter your own cash flow data instead.  Very easy.

New Charts

In recent years, many complained that Excel charts lacked innovation.  So, Microsoft responded with new chart formats in Excel 2016.  You can find five of them on the “Insert Chart” ribbon, or the “All Charts” dialog: Treemap, Waterfall, Box and Whisker, and Sunburst. These powerful charts will help you easily visualize common financial, statistical and hierarchical data.

  • Treemap Is an area-based visualization chart that allows you to display your data in a hierarchical fashion,while also displaying quantities for each category via area size. Each category is assigned a rectangle area with subcategory rectangles inside. When you assign a quantity to a category, the rectangle area size is displayed in proportion to that quantity. Also, the area size of the primary category is visualized as the total of its subcategories.
  • Waterfall (or Bridge Chart) This chart is useful for visualizing how an initial value is affected by potential positive or negative results. It allows you to display a running total as you add or subtract values. It’s used to show how an initial value (for example, net income) is affected by a series of positive or negative values. Positive and negative numbers are shown via color-coded columns to make it easy to decipher the chart. The initial and the final value columns are displayed on the horizontal axis, and the intermediate values are shown as floating columns.
  • The Box and Whisker Chart This type of chart is typically used for statistical analysis. For example, you could use it to compare medical trial results or teachers’ test scores. The Box and Whisker Chart helps you display multiple data into quartiles. It highlights the mean and outliers. The boxes typically have lines that extend vertically called “whiskers,” that indicate variability outside the upper and lower quartiles. Points outside those lines or whiskers are considered outliers.
  • The Sunburst Chart is useful for displaying hierarchical data. It’s like using a pie chart with greater impact. Each level of the hierarchy is represented by one circle. (The innermost circle is the top of the hierarchy.) A sunburst chart with multiple levels of categories shows how the outer rings relate to the inner rings. It’s great for showing how one ring is broken into its contributing pieces. It’s a “pretty” chart, and useful to get the attention of your audience.

Collaboration and Co-Authoring

You and your colleagues can open and work on the same Excel workbook when using the online version of Excel 2016. This is called co-authoring. With it you can see each other’s changes in a matter of seconds. And with certain versions you can see other people’s edits in different colors. Simply select “Share” in the upper-right corner, type email addresses, and then choose a cloud location.  Note:  Only one user at a time can make changes.  The file is locked when real-time editing is going on.  You must “take turns.”

Database Enhancements

This is probably for more advanced users. The new database enhancements make it easy to transform and query your data. You can quickly pull, connect and merge with other data sources and add-on programs like Power Pivot and Power Query. You’ll have more options for power queries, data modeling, using tables, one-click forecasting, one-button workbook sharing and reporting. (This is pretty technical, so feel free to contact us for assistance.)

Quick Analysis Tools

You can instantly create and automate different types of charts, or add miniature graphs called sparklines. (Sparklines are used to show a trend in your data. They draw attention to important items such as economic cycles and highlight the maximum and minimum values in different colors. This is a great way to visually depict trends in your worksheet data.)  With the Quick Analysis Toolset, you can also apply a table style, create PivotTables, quickly insert totals, and apply conditional formatting.

Some Helpful Shortcuts within Quick Analysis Tools

  • Formatting: You select your range on data, and in the bottom right you’ll get a pop-up menu providing you options to display your data. It’s a quick access for formatting.
  • Charts: Provides a shortcut to the chart area or graph you’ve constructed.  There are also recommended charts or graphs based on the type of data you’ve inputted.
  • Totals: There’s also a shortcut add specific calculations to adjacent cells in your worksheet. You can add more columns or rows, and with right and left arrows, you can scroll through and find other quick and easy functions within your data.
  • Tables: Here you can convert a range of data to a Pivot Table, generate different types of tables, and more. Your data will be visualized as you hover and click on it. If you choose a specific table, it will open in its own worksheet.  It’s a big timesaver, and something even a novice Excel user can benefit from.

Power Analytics

This is one of the big changes to Excel 2016.  The power tools are now included so you can gather data, apply insights and make better decisions as a result.  Power Query is an add-on you can use to extend the functions of Excel.

Forecast calculates or predicts a future value by using existing ones. You can use this function to predict future sales, inventory requirements, or consumer trends. When you create a forecast, Excel develops a new worksheet that contains both a table of the historical and predicted values, and a chart that displays this data. Forecast can help you predict things like future sales, inventory requirements, or consumer trends. Simply go to the data tab in the ribbon and click on Forecast Sheet.

Powermaps is a 3D visualization tool that allows you to compare rows and rows of data (it can handle more than a million rows).  It allows you to map data within a table and plot it on a map. You can compare temperatures, rainfall, populations and more using this interactive mapping tool. Other mapping tools outside of Microsoft’s will sometimes charge you to do this.  You can pin your data to an address or location.

Tell Me helps you get information you need immediately.  It’s a new version of Microsoft’s Office Assistant that’s much for intelligent.  Just type in your question, or tell the application what to do (like paste a chart or table). The more you use it, the more you’ll love it.   It’s also available in the new versions of Word, PowerPoint, and Outlook.

Smart Lookup is similar to “Ask Cortana” (in the Windows 10 browser.  Simply click on the Smart Lookup button (or press Alt+RR), and Excel will open an Insights task pane with information about the entry in the current cell of your worksheet under an Explore tab.

For a definition for a cell entry rather than related information about it, simply click the “Define” tab at the top of the Insights task pane. Excel will display a definition for the term contained in the current cell. If applicable, it will include the latest statistics about the defined term.  With Smart Lookup, you’ll gain new insights into your data.

If you have any questions about this information, Excel 2016, or other Microsoft products, contact the Microsoft experts at Intelice Solutions at: (301) 664-6800 info@intelice.com or https://www.intelice.com/contact-us/