In Excel 2007: Pivot Tables for Data Analysis, Microsoft Most Valuable Professional Cortés Farey helps dispel the common fear of the Pivot Table feature, by demonstrating how to use this powerful tool to discover valuable business intelligence. Cortés shows how to create Pivot Table reports from internal Excel data and outside data sources, use filters to focus on the most important data in the sheet, and make visual presentations of data using Pivot Chart reports. Exercise files accompany this course.
• Sorting across data sources to show relative importance Adding, removing, and positioning subtotals and grand totals Creating conditional formats to highlight subsets of data Using colour scales to emphasize specific information Adding a trendline to a PivotChart report Updating and refreshing PivotTable data sources.
This course will show you how to use pivot tables to make sense of your organization's data. I will begin by showing you how to create a pivot table from data already in your Excel workbooks. Then, using that knowledge as a base, I will demonstrate how to create pivot tables using data from an external source. We will enhance your ability to analyse your data by showing how to reorganize or pivot a pivot table. After you have organized your pivot table's data you can sort the data to show relative importance and relevance or create filters that enable you to focus on that data you need to develop useful insights into your business. This course will show you how to create conditional formats that highlight subsets of your data and to visualize your data using dynamic charts called pivot charts that are based on your pivot table data. In short, I will show you how to summarize and analyse data using one Excel's most powerful and overlooked features.
The skills acquired from this version are mostly applicable to newer versions of Excel.
If you think that Microsoft Excel is only for business purposes, you are wrong. Many use it for various personal needs at home too. Some of the features that you should understand about Microsoft Excel to unlock the potentiality of this application are working with spreadsheets, cell formatting, various menus and toolbars, entering of data and editing the same, navigating between various spreadsheets, formula calculation, inserting various functions like date/time, mathematical functions, using of various charts that are available, inserting, editing and deleting comments on cells, etc.
Computer Based Training (CBT) is a way to master MS Excel. This is where you take your own time to master the application. With the help of CBT, you get realistic, interactive and complete training. You get to learn right from what a beginner should learn to advanced level. Once the training is over, you would have mastered Microsoft Excel. You get to see simulations of Microsoft Excel application software. Some CBT also offer review quiz questions to gauge your understanding. CBT generally offer interactive, complete and comprehensive training. As it offers practice questions, you will be able to understand better and gain confidence in using the application. According to me, this is one of the best ways to learn Microsoft Excel.
The concept of spreadsheeting has been around for hundreds of years. The process can be described as the ability to arrange numbers, characters and other objects into rows and columns. It is most common that spreadsheets only contain numbers and text. The software version of spreadsheeting basically takes the manual processes described above and puts them into an electronic format. Whilst the accounting industry which most commonly uses spreadsheets they can be used in any situation.
Pivot Tables are Excel's best tool for data analysis and summarising long lists of data into chunks of information. By using the drag and drop approach to the data fields within the pivot table, you can get a different view of the data. It all depends on what questions you want answered.
Pivot Table reports are organized into fields and items. Fields are rows or columns of data. For example, if you want to summarize data by year, each year becomes a data field. Generally, text fields go on the left hand side.
To start off with, the data list that you use must be in the flat file format or data list feature that is available in excel 2003 onwards. This means that all field names should not be based on data values. In other words, don't have January, February, etc. Instead use the label 'month'. Once you have set the data up this way, you can pivot the data as if the months are the field names by adding the months field to the column part of the pivot.
Another cool feature is the ability to group items within the table. For example, you can convert months into quarters if the data needs aggregated in this way. You can copy any pivot table you make and paste it further along the same worksheet or in another worksheet. This is useful if you have created calculated fields and don't want to repeat the whole process. This way you can create a table to show specific information about the data.
The quickest way to learn is to get a hold of some data and start creating your own tables. They are not that difficult to create. With some practice, you should be able to create one in about a few minutes.
You can also use pivot charts to extend your analysis into a graphical format. You can even drag and drop data fields within the chart as well. They do come with limitations, especially the lack of ability to change the layout and formatting.