Live Webinar - What’s new in Docker Desktop Enterprise 3.0

closeClose
Excel Quick Tips for 2019

Top five Excel Tips for 2019

According to Robert Half Technology’s Benchmarking the Accounting and Finance Education report, 63% of US companies depend on Microsoft Excel and around 69% of companies whether big or small find accounting with Excel highly valuable. Excel is the go-to tool for millions of users when it comes to accounting, data analysis, and insights. Almost everyone has some basic knowledge and understanding of how to make the best use of Microsoft Excel, but surprisingly only a few people learn more than creating grids of text, numbers, and formulas specifying calculation. Unlike others, even if you consider yourself as a proficient Microsoft Excel user, there are few excellent Excel tips and tricks that you can learn in 2019 to be more effective and efficient when using the spreadsheet program.

Well, without any further ado, let’s roll up our sleeves and get a quick insight on some interesting Microsoft Excel hacks for 2019 that will save you lots of time and frustration. Having read the article, we are sure you will wonder how have you been using the spreadsheet program without knowing about these interesting excel hacks.

Glean Rich insights from your data with intelligence in Excel -Use Insights in Excel

Insights is a novel AI based feature introduced with the intent of making data analysis easy and intuitive for everyone. Insights tool works with long-time user favorite intelligent and result-oriented features such as Pivot Table Recommendations, Flash Fill, and Chart Recommendations. Insights is a machine learning and AI-based feature to identify data patterns that can help in decision-making.

Insights tool helps you identify and analyze new insights such as outliers, trends, and other meaningful analyses and data visualizations. As it is a feature driven by the power of machine learning, the tool provides advanced analysis outcomes as the usage grows. The more you use it, the better it gets with continuous learning. Insights tool cannot be used with data that has been converted to a Pivot Table and moreover, it works best with clean, formatted and well-organized data in an Excel Table It is pretty simple to use the Insights feature- install insights and then you can access the tool just like using any other tool on the Office Ribbon.

Excel Table Preview

Image Credit – WindowsCentral.com

To make use of the Insights feature, highlight the Excel Data Set and then click on the Insert tab of the Office Ribbon. If you have installed Insights tool, you will find it to be present in the middle of the Office Ribbon under the Insert tab as shown in this image. 

Excel Table Preview

Image Credit – WindowsCentral.com

As soon as you click on the Insights button, Excel will take some time to analyze the data and then open up a task pane showing you several visualizations based on the data being analyzed. 

Excel Table Preview

Image Credit – WindowsCentral.com

If you find any visualization that meets your analysis requirements, simply click on the Insert link and it will get inserted into your spreadsheet in a presentable form. 

Excel Table Preview

Image Credit – WindowsCentral.com

Create Formulas with the Excel Formula Builder

Excel provides multiple options for data calculation and manipulations. However, excel users struggle a lot when building formulas and also have trouble remembering those formulas or syntaxes. The Formula Builder tool lets users search and insert a function, provides help on any given function/formula, and allows you to add data to the defined arguments in the function/formula.

What does the Formula Builder do?

  • Describes formula usage for the given syntax.
  • Provides a quick overview of the formulas already available in Excel along with their usage.
  • Search function to look for a specific formula or function by keyword or name.

Formula Builder can be used by users to learn about new functions. For instance, if somebody gives you a workbook that makes use of a function or formula you are not familiar with. Formula builder can help you understand how it works. You need to simply turn on the Formula Builder and just click on the strange formula. The tool will show you how the formula and its arguments were built.

Users can make use of the Formula Builder by simply clicking on the fx button present on the Formula bar or use the keyboard shortcut Shift+F3.


Create Formulas with Excel

Image Credit – www.aslns.com

Publish to Power BI

It is easy to spot data trends with Power BI.  The in-built time intelligence features make it easy to view huge amounts of data in various attributes and dimensions within a few seconds. Users can work smarter and faster by directly publishing excel workbooks into the Power BI site. With a Power BI subscription, users can publish excel files to Power BI for creating highly interactive dashboards and reports based on the excel dataset. These insights can be shared with peers in your organization.

To begin using this Excel feature, the file needs to be saved on your system first. Then Click on File ->Publish>Publish to Power BI. Once the file is published to Power BI, users can click on the Go To Power BI button to view the file in the browser.


Excel Publish Preview

Image Credit - powerbi.microsoft.com

Filled Map Charts for Automatic Map Labeling

Earlier, it was very difficult to create a map for numeric data in Excel. Users had to insert a map graphic and after that manually insert text boxes on each area with numbers in them. With the introduction of the filled map chart type in excel, creating map charts with automatic map labeling has become much easier. This is a great excel feature for businesses that operate across various countries or states. Users can easily chart the data onto a map as long as they have the correct geographic data that can be used for map generation. A filled map chart in excel identifies countries, provinces, states, and postal codes in data labels.  To work with a filled map chart, click on the Insert tab > Select Maps -> Select Filled Map


Maps in Excel

Image Credit - myonlinetraininghub.com

Excel uses the Bing Map engine to plot map data so you will have to accept Bing’s data alert before the map can be loaded.

Excel Map of USA
Image Credit – dummies.com

Funnel Chart

Funnel chart is similar to a bar chart, however, the bars are centered to a form a funnel shape. Funnel chart gives the reader an easy to understand visual picture on the various stages in a process. For instance, if you want to view the sales pipeline to see of all the leads you followed how many resulted in a closed sales deal, a funnel chart could be a great option to visualize and extract the required data, something similar to this image. 


Funnel Chart Preview

Image Credit – support.office.com

The earlier versions of Excel did not support the creation of Funnel charts but now users can easily create a funnel chart by setting up the data in a format similar to the above image. The data to create a funnel chart should have at least one column that defines the multiple stages in the process and another column that specifies the values for each stage.

Once you have the data in the required format, select the data and then click on Insert -> Insert Waterfall, Funnel, Stock, Surface or Radar chart > Funnel


Excel Table Preview

Image Credit – support.office.com

We know these Excel tips and tricks will be handy in your daily life and make you more productive by helping you get more done in less time. Try them out for yourself.

So now you’re a 2019 excel pro view some of our IT Training courses.