Progent's Application Experts

Microsoft Excel has been available for more than three decades and for nearly all that time has been the world's most popular spreadsheet software. Excel's programmability, connectivity, and rich feature set have allowed it to become a popular development platform and Excel is now used to power custom and commercial applications of every imaginable type and in all industries.

In addition to being deployed as an out-of-the-box spreadsheet, Excel is widely used as a front-end to sophisticated data analysis and decision support programs built with Microsoft Visual Basic for Applications (VBA) and connected to powerful databases such as SQL Server. VBA is also a popular tool for creating custom macros that add to Excel's already extensive library of functions. In addition, Excel is often deployed as a number-crunching back-end to custom applications that can be quickly built with popular products such as Microsoft Access, or as an easily-maintained database for generating individualized form letters or emails with Microsoft Word. Excel's versatility is enhanced further by its ability to connect to real-time data sources through interfaces like DDE and RTD, allowing Excel to be used for monitoring events such as changes in equity prices.

Progent's Microsoft-certified application experts offer a broad range of online services to help you design, program, deploy, manage, troubleshoot and repair applications based on any version of Excel, including Office Excel and Microsoft 365 Excel. Progent can also help you connect Excel workbooks to other Office platforms such as Word and PowerPoint, set up collaboration solutions with SharePoint, migrate to newer Excel releases including versions for Apple iOS-powered iPhones and iPads and Android smartphones and tablets, and provide cost-effective online webinar training in Excel that can be customized for individuals or groups. In addition, Progent can help you identify and resolve compatibility issues between various releases of Office Excel and between Excel and other components of Microsoft Office and Microsoft 365. Progent's experts can provide on-demand support to clients looking for a quick fix to a stubborn problem related to Excel and Progent also offers full project management services for migrating or developing line-of-business applications based on Excel.

Microsoft Office Excel 2019/2016 and Office 365 Excel
Microsoft Excel 2019, Excel 2016 and Microsoft 365 Excel offer a range of enhancements over earlier versions including improved integration of business intelligence reporting tools, new charts and functions, and better support for multi-vendor smartphones and tablets. Notable improvements include:

• PivotTable Improvements
  PivotTable enhancements in Excel 2019 and Excel 2016 include automatic relationship detection for identifying and creating relationships among the tables behind for your workbook's data model, PivotChart drill-down buttons for zooming across time groupings of time and other hierarchical structures within your data, search in the PivotTable for quickly finding important fields across your complete data set, and smart rename for renaming tables and columns in your workbook's data model while automatically updating associated tables and calculations across your workbook, including worksheets and DAX formulas.
• New Personal Templates
  Users with active accounts on Exchange Server or Exchange Online can use the Calendar Insights template, introduced in Excel 2016, to view their calendar as a dashboard and analyze how their time is spent and how they can optimize your productivity. Other new templates include the My Cashflow template for tracking what users earn plus what and where they spend, and the Stock Analysis template for time-base stock analysis.
• Integrated Business Intelligence (BI) Tools
  Power Query and 3D Map (formerly called Power Map), which were previously available as separate add-in tools, are now integrated into Excel as native features. Progent offers comprehensive online and onsite Business Intelligence (BI) consulting support for Excel Power Pivot, Power Query, Power View and Power Map and for Microsoft's Power BI and Power BI Desktop, which are based on the same underlying technology. (Learn about Progent's Power BI business analytics consulting services.) To find out about Progent's consulting and application development services for BI reporting, refer to Business Intelligence (BI) Reporting consulting.
• Single-click Forecasting
  The FORECAST function, previously limited to linear forecasting, now supports forecasting based on the industry standard Exponential Smoothing (ETS) algorithm and is available as a one-click wizard that allows you to create forecast charts from your data series and to adjust key parameters such as seasonality and confidence intervals.
• New Charts
  Excel 2019 and Excel 2016 include modern-style charts including the Treemap, Sunburst, Waterfall, Histogram, Pareto, and Box and Whisker.
  
  Excel 2019 and Excel 2016 include a library of modern charts such as Waterfall and Sunburst charts

• Enhanced Support for Multiple Operating Systems and Device Types
  Office Excel 2019 and Excel 2016 allow you to switch seamlessly across Windows, Android, and Apple desktops, smartphones and tablets for viewing, updating, and presenting Excel documents with a consistent look and feel. Cross-platform compatibility in Office Excel 2019 and Excel 2016 includes full-fidelity touch screen support for all Windows 10 devices.
• Data Loss Protection (DLP)
  This security feature, already familiar in Outlook, was introduced in Excel 2016. DLP provides a real-time, policy-based scan for typical data patterns associated with sensitive data types such as social security, credit card, and bank account numbers. You can synchronize DLP policies from Microsoft 365 in Excel, Word, and PowerPoint and ensure unified policy enforcement across content in Exchange, SharePoint and OneDrive for Business.

• Single Document Interface (SDI)
  Each workbook in Excel 2013 has its own window frame that can be moved and resized without affecting other workbooks. This Single Document Interface (SDI) is familiar to Office Word users and makes it more convenient to work on multiple related workbooks and to take advantage of dual monitors.
• New Worksheet Functions
  Excel 2013 includes more than 50 new worksheet functions covering a broad range of areas including date and time, math and trig, stats, engineering, formula display, and logic. In addition, Excel 2013 adds web functions including functions for referencing REST (Representational State Transfer) web services.
  
• Quick Analysis Tool
  The new Quick Analysis button saves time by providing a fast way to create various kinds of charts, add sparklines, create PivotTables, insert totals, and apply table styles or conditional formatting. You can preview the results and finalize the action in a single click.
  
• Flash Fill
  The new Flash Fill feature streamlines data entry by analyzing a sample action and completing an entire task automatically without requiring you to create a formula. A typical example of using Flash Fill is to create a column of first or last names from a column of complete names by typing the first entry into the new column. Flash Fill can recognize the pattern and complete the operation instantly for the entire column. Other common tasks that can be automated by Flash Fill include joining first and last names or extracting components from dates.
• Recommended Charts
  The new Recommended Charts command makes it easier to select a chart that is the most appropriate for the data you want to analyze. Recommended Charts automatically displays a list of suitable charts and allows you to preview and select a chart in a few clicks. If you prefer a chart that is not on the recommended list you can use the All Charts command to see all the chart types available.
• Recommended PivotTables
  The Recommended PivotTables command in Excel 2013 provides suggestions for presenting data using a PivotTable, displays previews, and lets you select the layout that works best for your analysis. You can also create a PivotTable from scratch with the PivotTable command or use the old PivotChart Wizard.
• Integrated BI Tools: Power Pivot, Power Query, Power View, and Power Map
  The Office Professional Plus version of Excel 2013 includes a suite of business intelligence (BI) tools: Power Pivot, Power Query, Power View, and Power Map. Power Pivot allows you to build data models quickly, filter data during the import process, define custom hierarchies and KPIs as well as calculation fields, and create sophisticated formulas using the DAX language. Power Query enables you to find, access, transform, and merge data from a variety of corporate and public sources for analysis within Excel. Power View lets you build and interact with data visualization features such as charts and slicers contained in an Excel sheet. Power Map (now known as 3D Map), is a geospatial visualization tool that displays geographic and time-based data for easy analysis. To learn about Progent's consulting services for business intelligence reporting, see Business Intelligence (BI) Reporting consulting.
  
  Power View in Excel 2013 supports Power Map for advanced data visualization

• Inquire Add-in
  The Inquire add-in for Excel 2013 lets you compare workbooks by highlighting changes cell by cell, including VBA code changes, making it easy to see how a workbook has altered over time and facilitating audits. The Inquire tool also includes a Workbook Analysis command that helps you review a workbook's logic structure, data dependencies, and error status. To help you understand how multiple workbooks are related, Inquire can display an interactive Workbook Relationship Diagram of workbook dependencies created by links between files, which can include workbooks, Microsoft Access databases, text files, web pages, SQL Server databases, and other sources. To help you understand how worksheets depend on one another, the Worksheet Relationship Diagram provides a map of connections between worksheets that can be in the same workbook and in other workbooks. Inquire can also help you troubleshoot a workbook by detecting problems such as formula errors, broken links, and hidden data.
• SharePoint Server 2013 Integration
  You can publish Excel 2013 workbooks on a SharePoint Server by using SharePoint Server 2013's Excel Services. This provides centralized management and protection of published workbooks and allows users of SharePoint Server 2013 to access workbooks via a conventional browser. For business intelligence (BI) applications, Excel Services allows you to connect Excel workbooks to a variety of external data sources, create BI reports and dashboards, and publish workbooks to a SharePoint document library to provide all authorized stakeholders easy access to critical information.
• Collaboration and Cloud Integration
  Excel 2013 users with Skype for Business or Lync can present and view Excel workbooks in an online conference and can allow any participant to control the workbook. Excel 2013 also has built-in support for saving, sharing and co-authoring worksheets in the cloud with Microsoft 365 apps or free OneDrive service.

• Enhanced Pattern and Trend Analysis
  – Sparklines summarize data via miniature charts in a single cell next to your text data
  – Slicers give a concise visual summary of PivotTable and PivotChart views and CUBE functions
  – PivotChart displays different data views for analysis independent of PivotTable views
  – Conditional Formatting makes it easier to spot critical information
  
• PivotTable Improvements
  – Multithreading improves performance for PivotTable data retrieval, sorting, and filtering
  – What-if Analysis allows conditional PivotTable editing and sharing via OLAP or Analysis Services cube
  – Named sets tool allows creation of PivotTables that change based on dynamic filter sets
  – For OLAP PivotTable users, dynamic rendering is supported by using the filter in the matrix
  
• Increased User Productivity
  – More efficient navigation based on the Microsoft Fluent user interface and Microsoft Office Ribbon
  – The new Search Filter accelerates searching in tables, PivotChart and PivotTable views
  – The Backstage View improves file recovery and provides centralized workbook management
  
• More Capacity and Faster Performance
  – Excel 2010's support for 64-bit computing breaks through the 2 Gbyte dataset limit of Excel 2007
  – The free PowerPivot add-in speeds up modeling and analyzing very large, multi-source data sets
  
• Better Collaboration
  – Excel Web App allows people to edit the same workbook simultaneously via any Web browser
  – SharePoint Foundation 2010 allows collaboration on workbooks from within the firewall
  – Partners and consultants can use Windows Live ID for simultaneous workbook authoring
  – SharePoint Excel Services lets users share a single version of a workbooks via any web browser
  
• Improved Support for Remote and Mobile Users
  – Excel Mobile allows editing and viewing workbooks, including charts, from a Windows 7 smart phone
  – Microsoft Excel Web App allows location-independent workbook editing
  

Excel 2007 features significant improvements over Excel 2003. These include:

• Results-oriented User Interface. A results-oriented user interface makes it easy to work in Microsoft Office Excel 2007. Commands and features that were often buried in complex menus in Excel 2003 and toolbars are easier to find on task-oriented tabs that contain logical groups of commands and features. Many dialog boxes are replaced with drop-down galleries that display the available options, and descriptive tooltips or sample previews are provided to help you choose the right option.
• More Rows, Columns, and Other Limits. Excel 2007 supports up to 1 million rows and 16 thousand columns per worksheet. This represents 1,500% more rows and 6,300% more columns than Excel 2003. Instead of 4 thousand types of formatting, you can use an unlimited number in the same workbook, and the number of cell references per cell are increased from 8 thousand to limited by available memory. For improved performance, memory management has been increased from 1 GB of memory in Microsoft Office Excel 2003 to 2 GB in Office Excel 2007. Because Office Excel 2007 supports multiple processors and multithreaded chipsets, calculations are faster in large, formula-intensive worksheets.
• Rich Conditional Formatting. In Excel 2007, conditional formatting lets you visually annotate your data for both analytical and presentation purposes. To find exceptions and to spot important trends in your data, you can implement and manage multiple conditional formatting rules that apply rich visual formatting in the form of gradient colors, data bars, and icon sets to data that meets those rules.
• Easy Formula Writing. Improvements in formula writing include a resizable formula bar with more levels of nesting, function autocomplete for completing formula arguments, the ability to manage multiple named ranges in a central location, and structured references. In addition to cell references, Office Excel 2007 provides structured references that reference named ranges and tables in a formula.
• OLAP Formulas and Cube Functions. When you work with multidimensional databases (such as SQL Server Analysis Services) in Office Excel 2007, you can use OLAP formulas to build complex, free form, OLAP data bound reports. Cube functions are used to extract OLAP data (sets and values) from Analysis Services and display it in a cell. OLAP formulas can be generated when you convert PivotTable formulas to cell formulas or when you use AutoComplete for cube function arguments when you type formulas.
• Improved Sorting and Filtering. In Office Excel 2007, you can quickly arrange your worksheet data to find the answers that you need by using enhanced filtering and sorting. For example, you can sort data by color and by more than 3 (and up to 64) levels. You can also filter data by color or by dates, display more than 1000 items in the AutoFilter drop-down list, select multiple items to filter, and filter data in PivotTables.
• Excel Table Enhancement. The Excel 2007 user interface makes it easy to create, format, and expand an Excel table (known as an Excel list in Excel 2003) to organize the data on your worksheet. Improved functionality for tables includes table header rows, calculated columns, automatic AutoFiltering, structured references, total rows, and table styles.
• Shared Charting. In Office 2007, charting is shared between Excel, Word, and PowerPoint. Rather than using Microsoft Graph for charting, Word and PowerPoint 2007 incorporate the charting features of Excel. Because an Excel worksheet is used as the chart data sheet for Word and PowerPoint charts, shared charting provides more functionality to Excel, including the use of formulas, filtering, sorting, and the ability to link a chart to external data sources, such as Microsoft SQL Server and Analysis Services (OLAP), for up-to-date information in your chart. The Excel worksheet that contains the data of your chart can be stored in your Word document or PowerPoint presentation, or in a separate file to reduce the size of your documents.
• Easy-to-use PivotTables. In Excel 2007, PivotTables are easier to use than in earlier versions of Excel. By using the PivotTable user interface, the information you want to view about your data is just a few clicks away. You no longer have to drag data to drop zones that aren't always an easy target. Instead, you can select the fields that you want to see in a PivotTable field list.
• Quick Connections to External Data. In Excel 2007, you do not need to know the server or database names of corporate data sources. Instead, you can use Quicklaunch to select from a list of data sources that your administrator or workgroup expert has made available for you. A connection manager in Excel allows you to view all connections in a workbook and makes it easier to reuse a connection or to substitute a connection with another one.
• Additional File Formats. Microsoft Office 2007 introduced additional file formats for Word, Excel, and PowerPoint, known as the Office Open XML formats. These file formats facilitate integration with external data sources, and also offer reduced file sizes and improved data recovery. In Excel 2007, the default format for an Excel workbook is the Office Excel 2007 XML-based file format (.xlsx). Other available XML-based formats are the Excel 2007 XML-based and macro-enabled file format (.xlsm), the file format for an Excel template (.xltx), and the macro-enabled file format for an Excel template (.xltm). In addition to the XML-based file formats, Office Excel 2007 also introduces a binary version of the segmented compressed file format for large or complex workbooks. This file format, the Office Excel 2007 Binary (or BIFF12) file format (.xls), can be used for optimal performance and backward compatibility.
• Integration with Document Management Server. Excel Services can be integrated with Document Management Server to create a validation process around Excel reports and workbook calculation workflow actions, such as a cell-based notification or a workflow process based on a complex Excel calculation. You can also use Document Management Server to schedule nightly recalculation of a complex workbook model.

Progent's Cisco-certified CCIE engineers can help you set up an on-premises, cloud-connected, or hybrid network infrastructure that supports users inside and outside your firewall, and Progent's Hyper-V experts and VMware vSphere consultants can show you how to consolidate hardware, streamline management, and improve fault-tolerance and recoverability by setting up virtual servers enhanced with cluster technology. Progent's certified network security engineers can help you evaluate the security and compliance of your Excel environment, establish and enforce security policies based on industry best practices, and design a disaster recover strategy that maximizes the availability of your Excel-powered applications. Progent can also provide affordable online training classes for Office Excel that are customized for the specific needs of individuals or groups within your organization.

Contact Progent for Microsoft Office Excel Consulting
If you are looking for a Microsoft Excel consulting specialist, call Progent at 800-993-9400 or visit Contact Progent.

Attention IT Specialists
If you are a skilled consultant with demonstrated expertise in one or more of the applications promoted by the Experts Team, find out about how to collaborate with Progent by going to Progent's Experts Group.