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.

Excel: Speak Cell

Description: Speak Cell feature will use the system audio and read the data either in column wise or in row wise.
Steps:
1. Select Office button on the left top corner.



2. Select Excel Options.
3. Select Customize Tab from left side menu.
4. Select “All commands” from “Choose Commands from” combo box.




5. Add Speak Cells and the related options
6. Click OK
7. Point to the cell and select the Speak cell option.
Screen shots: Speak Cell

Excel 2007 : Go To Special

Description: Go To Special is not only used for moving to a particular cell, it can be used in multiple ways.

Row Difference

Feature used for finding the difference between column values. This will select all cells, which have a different from the first column
Select Row Difference option from the Go to special screen, it will high light the mismatched cells.
Other usages: Go to special can be used for removing Constants from an excel sheet or for removing the blank rows/cells, remove the formulas, etc.