Mastering Excel Pivot Tables


Excel pivot tables are very useful and powerful feature of MS Excel. They can be used to summarize, analyze, explore and present your data. In this page, you can learn about Excel Pivot Tables, How to use them, Examples Pivot Table Reports & More.

What is a Pivot Table?

Excel Pivot Tables help you take a table (or list) of data and then create a report from it, instantly.
For eg. you can take a bunch of sales data and then create a report on region-wise sales performance by Product.

How to Create a Pivot Table in Excel?

Creating a pivot table in Excel is very simple. Just follow these steps.
  1. Arrange your data in a table like fashion. Make sure there are no blank rows.
  2. Select your data and goto Insert > Pivot from Ribbon (or press ALT+DP)

Top 5 Tips on Pivot Tables

You can do a lot of reporting & analysis using Pivot Tables. Keep these 5 tips in mind next time you are using Pivot Tables. Click on the links to view the tip in detail.
      1. Drill down pivot tables: You can drill-down and get details by just double clicking on a value.
      2. Change Summary from Total: You can change summaries in Pivot Reports from Total to Count, Average or something else very easily. Just use Value Field settings.
      3. Slice & Dice Pivots: You can move anything to anywhere in pivot reports and Excel would instantly change the report layout and calculations.
      4. Difference from last month: You can easily display the difference from last month by changing value field settings.
      5. Calculated Fields in Pivots: You can make custom calculations in Pivot Reports by adding adding calculated fields.

      Advanced Pivot Table Techniques

      There are many things we can do with Pivot Tables to analyze data or do complex reporting. Go thru these advanced pivot table techniques to learn more.

      Create Monthly, Quarterly or Yearly reports from Daily Data:

      Using “Group” feature of Excel, you can easily create monthly or quarterly reports from daily data. Or, create show summaries by hour from a transaction data etc. The possibilities are many.

      Learn more about grouping dates in pivot reports.

      Use Report Filters to Create Many Reports from Same Data

      We can use report filters feature of Excel Pivot Tables to create multiple reports from same data in just a few clicks.
      Learn more about pivot table report filters.

      Use Slicers to create interactive dashboards in Excel

      In Excel , Microsoft has introduced a feature called as slicers. Think of slicers as filters, only more visual. So, if you add a slicer for department, you would see the list of all departments in box. You can just click on “marketing” to see the report for that department alone. We can use this feature to create an interactive dashboard in Excel, like below.

Comments

Post a Comment

Popular posts from this blog

Federal 1099 Filing Requirements (1099- MISC & 1099-K)

Image
In this part, let’s look at the key requirements for both the 1099-MISC and the 1099-K ( Pioneer Educator ). What payments do you need to report and where do you report them? Our goal is to help you better understand your obligation and all the required information you need to collect. Keep in mind there are many types of information returns used for reporting income, but we’ll focus on two of the most common information returns: the 1099-MISC and 1099-K. It is recommended to talk with a tax advisor to understand the reporting requirements for your particular circumstances. 1099-MISC Thresholds and Reporting Requirements In general, companies must send 1099-MISC forms if they’ve paid a nonemployee $600 or more during the year. But, there are other requirements depending on the type of payments and the relationship between the payor and payee. According to IRS guidance, a form 1099-MISC may be required if a company makes the following types of payments: • Royalties:  At l...
Read more

Usage IRS Form 1041

Image
For estate planning and taxation, IRS Form 1041,  U.S. Income Tax Return for Estates and Trusts , documents the income an estate earns after the estate owner passes away. It’s also used to track income before any recipients receive designated assets ( PioneerEducator ). Form 1041 is been used to account the income, deductions, gains, and losses of an estate or trust. If you are supporting clients in dealing with the estate of a deceased person, you may have to use Form 1041. Usage IRS Form 1041 For federal tax purposes, a trust or estate is treated as a separate legal entity, and the tax return can get pretty complicated. In addition to tracking gains and losses, Form 1041 is also used to report income that will be distributed to beneficiaries, income tax liability, and employment taxes on wages paid to household employees. Remember that not every estate needs to file the Form 1041, especially if it has no assets that produce income, or if the estate’s income is les...
Read more

Know your OSHA rights and Responsibilities

Image
What is OSHA? The United States Occupational Safety and Health Administration (OSHA) is an agency of the United States Department of Labour. It was created by Congress under the Occupational Safety and Health Act on December 29, 1970. Its mission is to prevent work-related injuries, illnesses, and deaths by issuing and enforcing rules (called standards) for workplace safety and health. whether you are an employee under the jurisdiction of Federal OSHA or an employee working in an OSHA State Plan State, the basic employee rights are the same: Employees have the right to a workplace free from serious safety and health hazards. Workers’ basic rights under OSHA include: • Working conditions that do not pose a risk of serious harm. • The ability to file a confidential complaint with OSHA to have their workplace inspected ( Pioneer Educator ). • Receiving information and training about hazards, methods to prevent harm, and the OSHA standards that apply to their workplace. T...
Read more