Excel : Paste Special

Description: Paste Special feature is having lot of inner features. In this session, let us look into few of them.

Skip Blanks
Consider the scenario where a salesman, who needs to travel across the country, wants to fill his attendance. He filled the details in the following format

Now he wants to merge these two sheets. Here, we can use the skip blank feature of paste special.

Steps:
1. Copy the Attendance details from one table.
2. Point in the first cell [first cell of the column, which needs to be merged] of the second table.
3. Right click and select Paste Special
4. Check the Skip blanks option
5. Click OK.

Result:

Math
Consider the scenario, where we want to change the price to 3.5% of the existing Price. Place the modified Price data in the Price column itself.

Steps:
1. Place 3.5 in a cell.
2. Copy the value
3. Point the first cell of the Price column
4. Right click and Select Paste Special
5. Select Multiply option under Operation
6. Click OK



Note: Use Add for adding a single value to the entire column, Divide for dividing the column data with a single value and Subtract for deducting particular value from column data.
Paste Special is also giving the options for Pasting only the Column Widths, Values, Formats, etc.

Source Width
Description: Copy data from one location to another will change the format, column widths, etc. For keeping the source data column width follow the steps below.

Steps:
1. Copy data/cells
2. Paste to the destination
3. Click on the Smart Tag appeared.



4. Options available are
• Keep Source Formatting
• Use destination Theme
• Match destination formatting
• values and Number formatting
• Keep Source Column Widths
• Formatting Only
• Link Cells
5. Select ‘Keep Source Column Widths’ option.

Excel 2010: Templates in Back Stage -> Front Stage for a regular user

Back Stage offers amazing functionalities for a regular user. Here we will discuss the templates available in the back stage; which will be helpful for a regular user.


Templates
Back stage provides a variety of templates ranging from Normal timesheet to Budgets. Also it provides sample templates with pre-populated data, which will be helpful for those who are preparing trainings.




One of the common scenarios is that people want to prepare rent receipt or any other receipt. Instead of learning the excel features end to end, he can choose one of the receipts templates provided by the Excel back stage.








I chose the donation receipt










Our receipt is ready with professional formats and functionality.

Excel 2010 : Slicer - New Filtering Technology

Today we will look into one of the new feature of Excel 2010 - Slicer.
Slicer is helpful to drill-down and analyze the data. Let us see how it will work.

Open the sales report.


Pivot table gives the in-built filter option and grouping too. Slicer is adding more drill-down options.
Insert the slicer from Insert -> Slicer.





You will get the option to select the column in which the drill-down needs to be done. Select the required field or fields; this will add one slicer per field. I am selecting two fields Product and Customer.





We can do the drill down by using one slicer or using both slicers.