Using Pivot Table Data in Summary Sheets

Learn Pivot Tables Easily

Pivot tables are a great boon in Excel. Previously, getting IT departments to write custom reports used to take ages. And even if you could get a report written for your needs, by the time you got it, it was too late, or you wanted to look at information from another angle.

With Pivot tables, you can now create your own reports in no time. And you can slice and dice information pretty easily, with just a few clicks.

However, even though Pivot tables are great, they are not the best tool  for presenting information for the senior management. There may be times when you want to pick up certain information from the pivot table, format it nicely, and present it with other summary figures.

To use the summarized data from the pivot table, but make it more presentable,  you can use an extremely useful function of Excel called the GetPivotData.

Enabling GetPivotData Button
It is very easy to get this function to work. This little gem is hidden right within the Pivot Toolbar. Just right click on the Pivot Toolbar, right at the end, and select customize. Pick Add/Remove Buttons.

Select GetPivotData button. This is a toggle button – click it once and it gets enabled, and another click disables it. You can see a slight change in the icon when it is enabled or disabled.

Once the button is highlighted, you can begin writing your formula. Start with a = sign in a black cell where you want a pivot table value. then point to any cell in the Pivot Table. Its value is captured in your formula.

As long as the data is available and visible in the Pivot table, you can move the data around from rows to columns or page fields, but it will still appear correctly in the presentation area.

Go ahead. Give it a try and make your data presentation summaries more dynamic and presentable. Any questions or comments, do let me know.

Cheers,
Vinai
Editor, Microsoft Excel Tips Blog

Do You Use These Advanced Features in Microsoft Excel?

Advanced Excel training at Intellisoft
Practical hands-on advanced excel training at Intellisoft
Practical hands-on advanced excel training at Intellisoft

Most people hardly use the huge number of features available in Microsoft Excel. Many are just using Excel as a calculator. This is a gross under use of Excel’s vast potential and feature rich functionality.

Do a quick check, and see if you use these advanced features of Microsoft Excel in your day to day work.

Some of the common things that can be done easily with Excel are:

  1. Finding the Top 10 Customers or Finding the Bottom 10 Performers in the organization
  2. Highlight values that are above or below a certain threshold – like all sales above $25,000 to be highlighted
  3. Sort the values in Ascending, Descending or any customized order – like sorting in order of Manufacturing, Accounts, Sales departments.
  4. Give Names to Range of Cells, and then use them in formulas for easy referencing and decoding
  5. Exploit Pivot Tables to Summarize the data and slice & dice it in any way – finding sales by product groups, or calculating productivity by department
  6. Write Macros to automate routine things that save you a huge amount of time – example creating pivots, charts, tables, and doing complex calculations automatically.
  7. Use advanced filtering conditions, and be able to filter data using multiple different criteria
  8. Create fantastic charts that portray the given business situation perfectly. There are over 50 different types of charts to choose from, and each has its edge, advantages and a reason.
  9. Create management dashboard that are dynamic, and provide a complete snapshot of the key business KPIs in the company – change the chart values at the click of a checkbox or change in a drop-down value
  10. Use Excel’s advanced What-If analysis to do projections for future, forecasting, trend analysis etc. with ease
  11. Use Lookup tables to find any value or corresponding value from a table using advanced functions and formulas

This is just the tip of the iceberg. Microsoft Excel is really extremely powerful. Each version of Microsoft Excel – be it Excel 2007, or Excel 2010 or Excel 2013 adds more and more features to the already powerful dynamite of a package.

At Intellisoft, we teach people how to leverage the maximum power out of Microsoft Excel in short training courses. Some of the popular courses are:

We have a number of Public Classes each month, and we also provided In-House Training to your staff and team at your office, if you have a group of 10+ people, and have a room to hold the training.

So what are you waiting for? If you would like to learn any one or more of such useful features of Microsoft Excel, come for a short Excel Training at Intellisoft.

Go ahead, equip your team with the right skills. Get everyone on board to learn the basic and advanced features of Microsoft Excel, and Be Awesome in Excel!

Email to training@intellisoft.com.sg or call +65-6296-2995 for the next available schedule of Microsoft Excel Training in Singapore.

We are located at Beach Road, in Singapore! Location Map of Intellisoft

Cheers,
Vinai Prakash, PMP, ITIL, Six Sigma, GAP,
Master Trainer

Creating Management Dashboards in Microsoft Excel

Today, Microsoft Excel has become extremely power tool for any data warrior. Whether you work in Sales, Marketing, Customer Service, Logistics, Accounts, Manufacturing, Finance or Administration, everyone must become an expert at using Microsoft Excel.

It is not sufficient to know Tables & Formulas in Excel. You must also know how to make sense of the data that you have.

Successful analysis of data, and presentation of data are vital skills for any office worker. Simple techniques to analyze data are to use Pivot Tables, Normal Data Ranges, and Tables.

Further, there are advanced data analysis tools within Excel – like the Scenario Manager, Goal Seek, Solver, Conditional Formatting, Slicers etc. that make the job of data analysis much easier.

However, it is not sufficient to merely analyze the data. It is important for you to also present the data using the latest and best in-built techniques within Excel. Most of the people who come for Excel Dashboard Training, or the Data Analysis Training With Excel are not aware of these features.

Microsoft Excel Dashboard Training

Microsoft Excel Dashboard Training

When you create an Excel Report, which allows for the Report to become interactive, allows the management to play with the data, change the parameters, and see the data change, the graphs update dynamically, then such a report is called a Dashboard.

DIFFERENCE BETWEEN REPORTS AND DASHBOARDS

The key different between a Report and a Dashboard is that a Report can be static, long, and without any interactivity.

In contrast, a Dashboard is a dynamic report, Short, Fits a page usually, and allows the users to change several parameters on the fly to compare the data from the past year, quarter, and play what if dynamically.

Dynamic Checkbox Dashboard With Microsoft Excel

Dynamic Dashboard With Microsoft Excel – Learn to Do It Yourself!

Intellisoft Systems in Singapore conducts Public and Customized Classes for Creating Dashboards With Microsoft Excel. Many companies have opted for Corporate Training, where the content can be customized to suit your audience and participants, plus their level of expertise with Microsoft Excel.

For the dashboard training, the Excel version is not that important. You can use Microsoft Excel 2010, 2013 or Microsoft Excel 2016 for the Excel Dashboard Training.

Plus, the Dashboard training can be conducted in your country. Simply contact Intellisoft at +65-6296-2995 for the training details.

Cheers,
Intellisoft Systems Team, GoldenMile Complex, #06-05,
Singapore 199588

Recommended Training: Advanced Excel 2013Excel Dashboards MasterClassData Interpretation & AnalysisCustomized Corporate Training

Free Tips, Tutorials & Training Grants Info

Learn from expert tips, tricks and resources for Excel, PowerPoint, Photoshop, Project Management, IT, Soft Skills & more with our Email Newsletter.
Plus get the latest news on Grants. Join Today!

Found What You Were
Looking For?

Just Tell us...

We're Here To Help You!