Pivot Table Guide – Value field settings

May 11th, 2013 by Tips Master | No Comments | Filed in Excel

Value field settings in pivotIts has been a quite long time since i have updated the links in the pivot table guide. I have been getting a lot of requests from users to get a complete guide to the pivot table. I am planning to complete the entire pivot table guide with in next couple of weeks . This week we will see the pivot table feature “value field settings”.

 

(more…)

Learn to use watch window in Excel 2007

May 27th, 2012 by Tips Master | 1 Comment | Filed in Excel

This tutorial explains how to use a watch window in excel 2007. Watch window is basically an auditing tool which will help you to see cells which are not visible on a work sheet. Watch window will allow you to see, inspect and audit the values as well as formulas in a cell which is not visible in the screen. It will help you from not wasting your time from scrolling down the window and check the values.

 

 

(more…)

Tags:

Various ways of data consolidation in Excel

July 18th, 2011 by Tips Master | No Comments | Filed in Excel

Data consolidation in ExcelFor the last few posts, I have been writing about data consolidation – Various ways possible of consolidating in Excel. I was getting a lot of queries on the same so thought of putting all those in a single place. This is the consolidation of all the previous post on the topic. You will also find sample worksheets with illustrations under each posts.  Don’t forget to do your home works with those .

 

(more…)

Tags:

Consolidate data in multiple worksheets in Excel

June 20th, 2011 by Tips Master | No Comments | Filed in Excel

Data Consolidation in ExcelTo summarize data from separate worksheets, you can use the data consolidation tool in Excel. The worksheets can be in different workbooks or in the same work book. There are two types on consolidation possible with this. One is consolidation by position and the other is consolidation by category. This is a very useful tool in Excel which will help in data consolidation. We will see detailed illustration on how to do this in Excel 2007.

 

 

(more…)

Tags:

Adding a calculated field to Excel Pivot table

June 15th, 2011 by Tips Master | 2 Comments | Filed in Excel

Adding calculated field to Pivot tablePivot tables are designed to help users crunch and analyse large amounts of data. Today we will learn                       a very useful feature in Excel pivot tables, which is adding calculated fields. Calculated fields can be used when we need to create some custom fields which was not there in the base data. In this tutorial we will see how to insert a calculated field in Excel 2007, modifying or deleting it and what are the benefits and drawbacks in using the calculated fields.

(more…)

Tags:

Excel Challenge – How to paste data to a filtered list in Excel

June 9th, 2011 by Tips Master | 11 Comments | Filed in Excel

ChallegeHow to copy paste data from a filtered list to another filtered list? This is a constant query in many of the Excel forums. I have tried my way of solution for this. Readers can also take this as a challenge and come with your solutions in the comments. No VBA please.

 

 

 

 

(more…)

Tags:

Data consolidation in Excel made easy using Pivot tables

June 6th, 2011 by Tips Master | 6 Comments | Filed in Excel

Consolidating multiple ranges in PivotConsolidating multiple ranges using Pivot table is a very useful function in Excel which will help to consolidating values from different sheets. This is an alternative to consolidating data using Sum product explained in the previous posts. Data consolidation is possible in Excel using data Consolidation function, consolidating multiple ranges using Pivot tables and using 3D functions.

 

 

 

 

(more…)

Tags:

Useful Tips in Excel – Deleting blank rows, Inserting blank rows, finding out difference in Time

June 4th, 2011 by Tips Master | 4 Comments | Filed in Excel

Helpful Tips in ExcelYou will find some easy and quick Excel tips which will help in your routine work.

Tips explained here will help you when

1. Working with dates and time

2. Inserting blank rows within data

3. Removing blank rows from data

 

 

(more…)

Data Validation – Creating Dependent drop down list in Excel

May 31st, 2011 by Tips Master | 9 Comments | Filed in Excel, Excel Functions

Data validation in ExcelAfter reading the last post, explaining on how to create a linked list using Excel data Tables, I am getting a lot of mails asking about creating dependent list using Indirect function. We will see today how to create a dependent list in Excel data validation using Indirect function. This is a very short and quick tutorial when the list is very small and does not contains any duplicates. If the list contains duplicate and lot of items , its suggested to use the method explained for avoiding the duplicates in Excel data validation.

 

 

(more…)

Avoid duplicates in data validation and create a linked list in Excel

May 30th, 2011 by Tips Master | 5 Comments | Filed in Excel, Excel Functions

Data validation - Linked list and avoid duplicate valuesThis tutorial explains how to create a linked list in Excel. Linked list or a depended list means you have drop down list where one drop down depends on the value of the other. We need to restrict the values based on the value selected from the first list. Here I have attempted to create a linked list  using Excel tables.  There are other ways for creating Linked list , for e.g. using Indirect function. But the disadvantage is that when the database runs in to a large number of lines you will find it difficult to add each one two different names and also creating new names for each combination. Also explains how to avoid duplicate values in a data validation. Don’t miss this one…

(more…)