All Excel Templates Normally an 80% saving at $299.00. Super Special $175.00!
***ADVERTIZEMENT***
Check out this months Discount price offer !
MAIN INDEXWhat's New at OzGrid | Discount Offer | Excel Tips and Tricks | Excel VBA Tips and Tricks
This month we are proud to sponsorSmartDraw . This award-winning software is perfect for all your business charts and diagrams. As a newsletter member, you are eligible for a limited-time 15% discount! Todownload free demos go here
***ADVERTIZEMENT***
$5.99 Training What's New at OzGrid
Finally, at the end of March all the hard work and effort that we have put in over the last six months came into fruition and our book, Excel Hacks - 100 Industrial Strength Tips & Tricks went to the publishers. It looks great, and we are very pleased with the finished product. It was released last week and so far the feedback has been fantastic and sales have been good. We will keep you informed as to how it goes over the next few issues of the newsletter. Of course, you can purchase a copy here if wish.
After Easter we took a well deserved family break and have come back totally relaxed and rejuvenated (mind you it is only Day 1). Now the book is finally complete, over the next few months, our next big project is to update all of ourExcel Training Lessons to reflect any new features in Excel 2003 . This will certainly keep us busy, although we still expect 97, 2000 and 2002 lessons to sell well for some time to come.
That's all for this month, we hope you enjoy April's newsletter .................
This month I will discuss what is arguably one of Excel's most powerful and useful features, Pivot Tables. If you learn about Pivot Tables and stick with good spreadsheet design, you will soon come to realize why Excel is the number 1 spreadsheet program in the world!
**USEFUL PIVOT TABLE LINKS**
I won't try and hide the fact from you that I am a big fan of Pivot Tables. I use them a lot in the development of Spreadsheets for my clients. Once the client sees the Pivot Table, they nearly always ask "could I do that?" the answer of course is yes! Unfortunately most people tend to shy away from Pivot Tables, as they see them as too complex. To be honest with you, when you first use a Pivot Table the whole thing can seem a bit daunting. Don't be put off by this as persistence will almost certainly pay off. I find the best part about Pivot Tables is their ability to be manipulated via 'Trial-and-Error' and immediately show the result. If it is not what you expect, simply use the Undo feature and/or have another go! What you must always remember is that you are not changing the structure of your original table in any way at all, so you can do no harm!
WHY are they called Pivot Tables ? - Well, basically they allow us to pivot our data via drag-and-drop to produce meaningful information. This makes Pivot Tables interactive in that once the table is complete we can very easily see what effect moving (or pivoting) our data has on our information. This will become patently clear once you give Pivot Tables a go. Believe me, no matter how experienced you get at Pivot Tables there will always be an element of trial-and-error involved in producing the desired results! What this means is you will find yourself pivoting your table a lot!
WHAT would we use them for ? - We would use a Pivot Table to produce meaningful information from a table of information. Imagine you have a table of data that contains names, addresses, ages, occupations, phone numbers, postcodes etc. With a Pivot Table we could very easily and quickly find out:
In fact the list can go on and on!
WHAT is the advantage ? - Perhaps the biggest advantage to using Pivot Tables is the fact that we can generate and extract meaningful information from a large table of information within a matter of minutes. Or perhaps it is because they will not use up a lot of memory from your PC. In a lot of cases we could get the same results from a table of data by using Excels built in functions, but this would take more time and use far more memory. On top of this, if we wanted some new information we can simply drag-and-drop (pivot). We can also opt to have our information update each time we open the Workbook and/or by clicking refresh.
Pivot Charts - In Excel 2000 Microsoft introduced Pivot Charts. These are simply charts that are read from the table created via the Pivot Table Wizard. In fact Pivot Tables are really no longer just Pivot Tables, they are now PivotTables and PivotChart Reports. These two features go hand-in-hand with each other. By this I mean, when you create a Pivot Table you can also create a Pivot Chart at the same time with no extra effort needed on your part. Pivot Charts allow us to create professional interactive charts that were previously not possible without either VBA or using Excel's Controls.
***ALL IMPORTANT Table and List Guidelines for Pivot Tables***
The most important factors when considering using a Pivot Table is to have our data set up in what could be termed as a table and/or list. As our Pivot Table will be basing all its data on this list or table it is vital that we set our tables and lists up in a uniform way.
In the context we are discussing here, a Table is no more than a List (with a heading) with more than one Column of data and a different heading for each column. A List is often referred to in the context of a Table as well. The 'good practice' that applies to setting up a List will aid us greatly when we need to apply a Pivot Table to our Data. When we extract data via the use of Lookup functions or Database functions we can be a little less stringent in how we set up our Table or List. This is because we can always compensate with the aid of a function and probably still get our result. However, having said this we should always set up our List or Table as well as possible. When we use Excels built-in features they will and do assume a lot about the layout and setting up of your data. They do have a degree of flexibility but more often than not you will find it easier to follow the guidelines for setting up your Table or List.
Let's look at what I believe to be the most important aspects of setting up a Table or List.
If we follow these guidelines as close as possible, using Pivot Tables will be a relatively easy task.
Whenever we create a Pivot Table and Chart we use what is known as the Pivot Table and Pivot Chart Wizard. This guides us through the creation of our Pivot Table using a four step process. In a nutshell these steps are:
Once we have done this three step process we end up with a empty Pivot Table ready to accept our data.
There are also many side-steps we can take along the way to manipulate our Pivot Table, but most users may find it easier to do this after step 4 There is however a slight difference with Excel 97 and 2000 + in that there are 4 steps in Excel 97. Step 3 in Excel 97 is where you can set-up your data, this can still be done in Excel 2000 +, but you have a choice of doing this after the wizard has completed.
***Database Software **
This month I thought I would share with you some custom functions that you can install as an Excel Add-ins (.xla) and use as needed. This Excel Add-in is an open source and you are free to use them and the code for your personal use. If you are unsure of how to install an Excel Add-in go here
Once the add-in is installed, you should then go to Insert>Function and select "User Defined" from the Category and you will then have 19 additional functions.
Over the next few issues of our newsletter I will be showing examples of what the functions can be used for. Most however are self-explanatory.
Download the free functions add-in here
**Please ONLY use our free Excel forum for any questions pertaining to these functions.**
Until next month, keep Excelling!
You are more than welcome to pass on this newsletter to as many people as you wish. Or even upload it (as is) to your Web site!
To no longer receive our newsletter, send a NEW email with the exact words "action: Unsubscribe" in the body of the email, or click here .
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation
Read this issue and past issues online here :