Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!

2. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.

# Pivot Tables Explained

If you have never heard of Pivot Tables you must read this newsletter to have pivot tables explained. If you know of Pivot Tables, read the bottom section.

We are running a 1 day Pivot Table course on Friday 11th April at the Bryanston Country Club. Contact us for more information.

In this issue:

# Pivot Tables

Pivot Tables are without doubt the most important tool in Excel. Over the versions, Pivot Table power has increased and be added to but even the original version from years ago was an unbelievably powerful tool.

Why should you care? If you do any of these, you can do it better, faster and safer with Pivot Tables

• reconcile debtors, creditors, credit card and bank accounts
• summarise payroll data
• analyse sales listings to identify trends.
• manage project costs and allow for detailed reporting and analysis.
• identify trends in data

In fact where ever you have

• a large listing of data that you
• sort or
• filter and then
• Copy Paste, Copy Paste
• to get the reports you want,
• Pivot Tables are the answer.

To quickly remove some misconceptions:

1. Pivot Tables are NOT an advanced skill. An intermediate user can use Pivot Tables. You can learn how to use them! Not convinced? If you skill score is above 3, you can easily use them
2. Pivot Tables mean that you DON’T need to rely on tons of SUMIFS and COUNTIFS formula. If those functions confuse you, Pivot Tables are especially for you.
3. Pivot tables are useful when your bosses continually change their minds. You just drag a few items and suddenly you have a new report (which hopefully keeps them happy).
4. Pivot Tables are ideal for dashboards. If you do any sort of monthly management reporting you MUST use Pivot Tables in some way.

We are running a 1 day Pivot Table course on Friday 11th April at the Bryanston Country Club for R3 000. Contact adrian@AuditExcel.co.za for more information.

# What is a Pivot Table

A pivot table enables you to take lots of data that may have thousands of rows of data and turn it into an useful report in about a minute and about 10 clicks with no formula or typing

Most importantly, to change a report is effortless and you don’t need to worry about it maintaining its integrity.

To see this in action click https://www.auditexcel.co.za/video-tutorial/what-is-a-pivot-table/

We are running a 1 day Pivot Table course on Friday 11th April at the Bryanston Country Club for R3 000. Contact adrian@AuditExcel.co.za for more information.

# The Hidden Power of Pivot Tables- for people already using them

Even people who are familiar with Pivot Tables don’t know all its capabilities and where you can use them.

If you build a pivot table and then link cells into the pivot tables to calculate the percentage of the column or cumulative totals, be aware that this can all be done INSIDE the Pivot Table with more reliability then your formula.

Watch how you can do this on https://www.auditexcel.co.za/video-tutorial/pivot-value-settings-and-options/

Did you also know that within the Pivot Table you can:

• Build conditional formatting
• Build formula to calculate WITHIN the pivot table
• Group data by years, month, quarters, weeks, hours, minutes, seconds
• Refer into the pivot tables with a special formula (not VLOOKUP) so that it always finds the correct information.
• Tell Excel to repeat the items labels so that you don’t need to fill in all the blanks

We are running a 1 day Pivot Table course on Friday 11th April at the Bryanston Country Club for R3 000. Contact adrian@AuditExcel.co.za for more information.

Free Skills Assessment

If you haven’t already completed your free skills assessment (if you don’t have a skill score next to your name we don’t have one yet) why not answer these 20 questions to find out where you may need more knowledge.

For Pivot Tables you need a score of 3 or above. What is yours?

If you know of anyone who wants to improve their Excel skills, please tell them about the online version of the Excel Skills questionnaire where you can get an indicative score with only 5 questions.

# Combining Excel and Access

MS Access is a common database program available within the Office suite of products. Due to the power of Excel, Access is often forgotten as an option. However, there are times when Access, or a similar database program, would be the better option. Read more here on how to choose which one you should use

# Access with Excel course

We have created a 3 day Access with Excel course which covers the fundamentals of Access and how to use it, and also shows how to get the best out of combining Access with Excel. Read more about it here

# Advanced Excel Training

We are running an Advanced Excel course from 12th– 14th May in Midrand and this includes how to use Pivot Tables.

Our Advanced Excel course will take you from an intermediate user to an advanced user and focuses on the practical uses of Excel with real world examples from our consulting client.

This course is more advanced than the typical Excel level 3 and is beneficial for anyone who works with lots of data and needs to regularly clean the data received and turn it into meaningful information. Read more here

Please follow and Like us on Facebook, Twitter , Google + or LinkedIn

# Next Excel Courses

Email us if you need more details or visit the calendar page