Posts

Pivot Tables

Image
  Pivot Tables   Insert a Pivot Table | Drag fields | Sort | Filter | Change Summary Calculation | Two-dimensional Pivot Table Pivot tables  are one of  Excel 's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set. Our data set consists of 213 records and 6 fields. Order ID, Product, Category, Amount, Date and Country. Insert a Pivot Table To insert a  pivot table , execute the following steps. 1. Click any single cell inside the data set. 2. On the Insert tab, in the Tables group, click PivotTable. The following dialog box appears. Excel automatically selects the data for you. The default location for a new pivot table is New Worksheet. 3. Click OK. Drag fields The  PivotTable Fields pane  appears. To get the total amount exported of each product, drag the following fields to the different areas. 1. Product field to the Rows area. 2. Amount field to the Values area. 3. Country fie...
Post a Comment
Read more

What are pivot tables used for

Image
  What are pivot tables used for? If you're still feeling a bit confused about what pivot tables actually do, don't worry. This is one of those technologies that are much easier to understand once you've seen it in action. The purpose of pivot tables is to offer user-friendly ways to quickly summarize large amounts of data. They can be used to better understand, display, and analyze numerical data in detail — and can help identify and answer unanticipated questions surrounding it. Here are seven hypothetical scenarios where a pivot table could be a solution: 1. Comparing sales totals of different products. Say you have a worksheet that contains monthly sales data for three different products — product 1, product 2, and product 3 — and you want to figure out which of the three has been bringing in the most bucks. You could, of course, look through the worksheet and manually add the corresponding sales figure to a running total every time product 1 appears. You could then do ...
Post a Comment
Read more

VLOOKUP function

Image
 VLOOKUP function Show All Hide All This article describes the formula syntax and usage of the VLOOKUP function  (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel. Description You can use the VLOOKUP function to search the first column of a range  (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells, and then return a value from any cell on the same row of the range. For example, suppose that you have a list of employees contained in the range A2:C10. The employees' ID numbers are stored in the first column of the range, as shown in the following illustration. If you know the employee's ID number, you can use the VLOOKUP function to return either the department or the name of that employee. To...
Post a Comment
Read more