Excel Tips and Tricks for Daily Use

Excel Tips and Tricks for daily use

Inserting comments

You can insert a comment to give feedback about the content of a cell.

Insert Comment

To insert a comment, execute the following steps.

Select a cell.
Right click and then click Insert Comment.

Practice File: comments

Protect Sheet

When you share a file with other users, you may want to protect a worksheet to help prevent it from being changed.

Right click on the worksheet tab of the worksheet you want to protect.
2. Click Protect Sheet…
Enter a password.
4. Check the actions you allow the users of your worksheet to perform.
5. Click OK.
Confirm the password and click OK.
Your worksheet is protected now. The password for the downloadable Excel file is easy
Note: to unprotect a worksheet, right click on the worksheet tab again and click Unprotect Sheet..

Practice file: protect-sheet

Protect Workbook

Protects your sheet from Deletion.

Password while opening the file

Without giving correct password user cant open the file.

Open the Excel file in which you would like to add the password.
Press F12 and it will open the Save as dailog box as shown in the below screen.

Freeze / Unfreeze

If you have a large table of data in Excel, it can be useful to freeze rows or columns. This way you can keep rows or columns visible while scrolling through the rest of the worksheet

To freeze the top row, execute the following steps.
1. On the View tab, click Freeze Panes, Freeze Top Row.

Scroll down to the rest of the worksheet.

Result. Excel automatically adds a black horizontal line to indicate that the top row is frozen.

Unfreeze Panes

Practice file: freeze-panes

Sharing file

If you share a workbook, you can work with other people on the same workbook at the same time. The workbook should be saved to a network location where other people can open it. You can keep track of the changes other people make and accept or reject those changes.

To share a workbook, execute the following steps.
1. Open a workbook. 2. On the Review tab, in the Changes group, click Share Workbook.Practice file: share-workbooks

View multiple Excel workbooks

If you want to view multiple Excel workbooks at the same time, execute the following steps.

First, open two or more workbooks.
2. On the View tab, click Arrange All.
Select the desired arrange setting. For example, click Horizontal.
4. Click OK.
To switch back to normal view, click Arrange All and check ‘Windows of active workbook’.

Recording & Running Macros (Non Programming)

A macro is a customized collection of commands which can be executed on demand to carry out aparticular action in Excel. Since macros can include many different commands which aresubsequently performed automatically by the computer, they are very useful for performingrepetitive or complex tasks and for saving time.

First Plan Your Macro

Before recording a macro plan exactly what you want it to do and practice carrying out theprocedure manually. You might find it useful to write down the steps involved so that you don’tmake any mistakes when making the recording. Think about how you will use the macro and try togeneralise the commands so that your macro will work in as many different situations as possible.

3 Steps in creating macros

à1) Start Recording 2) Do the actions 3) Stop recording

Select the Cell from where you want to start the Macro Recording
Click on View Ribbon & then click on Macro, as shown in below screen.

Decide Where to Store the MacroàThe dialog offers three places to store the macro…

Choose This Workbookàif you want to store the macro in the current workbook. Themacro will be available to use in any workbook as long as the current workbook is open. Ifyou copy the workbook to somewhere else, or give it to someone, the macro will go with it.
Choose Personal Macro Workbookà To make the macro available to use in any workbookon the current computer (or, depending on network setup, on any computer when you arelogged in).
This is useful if only you are going to use the macro.
Choose New Workbookàif you don’t want to use either of the other options, perhaps

because you want to create an Excel Add-In to distribute the macro to other users.

Sparklines(2010 & above version only)

Excel 2010 makes it possible to insert sparklines. Sparklines are graphs that fit in one cell and give you information about the data.

To insert sparklines, execute the following steps.
1. Select the cells where you want the sparklines to appear. In this example, we select the range G2:G4.

On the Insert tab, in the Sparklines group, click Line.
Click in the Data Range box and select the range A2:F4.Practice file:sparklines
Click OK.

Hyperlinking

To different sheets
To same sheets
To excel file
Linking formulas with =

Hyperlink to sheets of same file & also to other files or different applications.

Shortcut of Hyperlink: CtrlK

Inserting dynamic table

When you create a table (previously known as list) in a Microsoft Excel worksheet you can manage and analyze the data in that table independently. For example, you can filter table columns, add a row for totals, apply table formatting, automatic freeze & dynamic range for table & vlookup formula also you can publish a table to a server that is running Windows SharePoint Services 3.0 or Microsoft SharePoint Foundation 2010.

If you no longer want to work with your data in a table, you can convert the table to a regular range of data while keeping any table style formatting that you applied. When you no longer need a table and the data that it contains, you can delete it.

Create a table
You can use one of two ways to create a table. You can either insert a table in the default table style or you can format your data as a table in a style that you choose.

Insert a table using the default table style

On a worksheet, select the range of cells that you want to include in the table. The cells can be empty or can contain data.
On the Insert tab, in the Tables group, click Table.

Keyboard shortcut: CTRL+L OR CTRL+T

If the selected range contains data that you want to display as table headers,
select the My table has header

Paste Special

Copy specific cell contents or attributes in a worksheet.

You can copy and paste specific cell contents (such as formulas, formats, or comments) from the Clipboard in a worksheet by using the Paste Special command.

Steps:

On a worksheet, select the cells that contain the data or attributes that you want to copy.
On the Home tab, in the Clipboard group, click Copy. Or Press CTRL C
Select the other sheet or any other cell where you want to paste
Press CTRL ALT V to paste

Click this option	To
All	Paste all cell contents and formatting.
Formulas	Paste only the formulas as entered in the formula bar.
Values	Paste only the values as displayed in the cells.
Formats	Paste only cell formatting.
Comments	Paste only comments attached to the cell.
Validation	Paste data validation rules for the copied cells to the paste area.
Column widths	Paste the width of one column or range of columns to another column or range of columns.
Values and number formats	Paste only values and number formatting options from the selected cells.
Transpose	*To convert Horizontal data in Vertical*

Auditing

Formula auditing in Excel allows you to display the relationship between formulas and cells. The example below helps you master Formula Auditing quickly and easily.

Trace Precedents

You have to pay $96.00. To show arrows that indicate which cells are used to calculate this value, execute the following steps.

Select cell C13.
On the Formulas tab, in the Formula Auditing group, click Trace Precedents.

Result:

As expected, Total cost and Group size are used to calculate the Cost per person.

Click Trace Precedents again.

As expected, the different costs are used to calculate the Total cost.

Remove Arrows

To remove the arrows, execute the following steps.

On the Formulas tab, in the Formula Auditing group, click Remove Arrows.

Trace Dependents

To show arrows that indicate which cells depend on a selected cell, execute the following steps.

Select cell C12.
On the Formulas tab, in the Formula Auditing group, click Trace Dependents.

Result:

As expected, the Cost per person depends on the Group size.

Show Formulas

By default, Excel shows the results of formulas. To show the formulas instead of their results, execute the following steps.

On the Formulas tab, in the Formula Auditing group, click Show Formulas.

Result:

Note: instead of clicking Show Formulas, you can also press CTRL + (`). You can find this key above the tab key.

Error Checking

To check for common errors that occur in formulas, execute the following steps.

Enter the value 0 into cell C12.
On the Formulas tab, in the Formula Auditing group, click Error Checking.

Result. Excel finds an error in cell C13. The formula tries to divide a number by 0.

Evaluate Formula

To debug a formula by evaluating each part of the formula individually, execute the following steps.

Select cell C13.
On the Formulas tab, in the Formula Auditing group, click Evaluate Formula.
Click Evaluate four times.

Excel shows the formula result.

Excel Shortcuts

Sr.	Excel Shortcut	Effect (Description)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

Formulas

Sr.	Formulas
1	Sum, Subtotal &SumProduct
2	Count, CountA, CountBlank, CountIF&CountIFS
3	SumIF&SumIFS
4	Upper, Lower & Proper
5	Networkdays&Edate
6	IF Condition, Nested IF and using AND – OR in IF – Calculation in IF
7	Len, Trim, Text & Concatenate
8	Round, Roundup &Rounddown
9	Today & Now
10	Vlookup&Hlookup
11	Using IFError in Vlookup& Multiple Vlookup