Simple Excel Tips and Tricks

Excel is software developed by Microsoft and is one of the most essential parts of Microsoft Office. It is a spreadsheet application capable of performing mathematical calculations, storing data in form of rows and columns and a lot of business applications.

Excel can perform calculations and manipulation in various fields including functions in fields of statistical, engineering and financial fields. It’s also used to represent (display) data in the form of graphs, charts, and histograms and also supports three-dimensional graphical display.

Information is stored in rows and columns of the excel sheet which have definitive naming conventions. Some tips and tricks can save lot of time and work overhead when working with your spreadsheet.

Tips and Tricks

• To find the sum of rows or columns quickly, you can click the first empty cell in the column and then press “ALT” + “=” (equal key) to add up numbers in every cell above.
• You can format a number by pressing “CTRL” + “Shift” + “4” to convert in percentage and “CTRL” + “shift” + “5” to format the number in percentage.
• Formula used in the cells can be seen by pressing “CTRL” + “`” (acute ascent key) and this is useful where complex calculations are being done.
• To jump to the start of the column, a combination of “CTRL” + “Shift” + “Arrow up” key is used and to Jump to end of column “CTRL” + “Shift” + ”Arrow down” key is used.  We can also double click on the first row or column to reach the last cell.
• Copying the pattern of numbers or Event Dates: Excel recognizes the pattern of values being entered and therefore, it automatically files the rest of the pattern in the remaining cells. Enter the values in two rows and establish a pattern, highlight the rows and drag down for any number of cells. This is useful in case of numbers, weeks, months and years.

Formulas

• Performing Mathematical Operations:

To perform mathematical operations, formulas have to be written. In Excel, the formula starts with “=” sign followed by the column or row names to be added.

For Example: Adding two C1 and D1.

Formula: =C1+D1

Steps:

• Type equal sign in E1
• Click on cell C1 with mouse pointer
• Type the plus in cell where the result is to be displayed, for instance E1
• Click on D1 with the mouse pointer
• Press ENTER key
• The answer would be visible on E1

Similarly, all the other basic mathematic operation can be performed using these steps.

• Performing calculation using IF function:

It is basically a conditional function and it checks for the true or false value.

Syntax: IF (logic, if true, if false)

Here the logic is the conditional check between two values. Comparison operators are used to check the conditions.

For Example

=IF(B4 < 2900, B4 * 10%, B4 * 20%)

This statement checks if the value of B4 is less than 2900. If the condition is true, it multiplies the value by 10% and if it’s a false function, it multiplies the value by 20%.

Comma separator cannot be used for large values for instance 29,600 because “if” statement uses the comma separator to separate three sections of “IF” statement.

• Using absolute relative referencing: With this, once the formulae are returned it can be copied to other rows or columns. On copying, the formula automatically refers to the new location in relative manner.

For example: if in Cell C13 Formula is =SUM(C1:C13), then after copying to cell D it would become =SUM(D1:D12)

• Removing the error:

Sometimes a formula returns error when the argument is not found or cell is empty such as “#REF!” or “#DIV/0”. To prevent these errors we use If(ISERROR(C2/D2),””,C2/D2). “ISERROR” returns true if error is encountered and in this case “if” statement would return an empty string. We can give any value instead of empty string.

• Cell constants: This helps you simplify the formula as it prevents frequent cell referencing in the formulas.

1. Steps to follow:
2. Choose Formulas—> Defined Names—>Define Name to new name dialog box.
3. Type the name of the constant
4. Specify Workbook as the scope for the constant.
5. Click the Refers To field, delete the content and replace it with a formula, Example =10%
6. Click ok to close the box.

• Locating all merged cells

To find merged cells, the steps are:

1. Press “CTRL” + “F” to Find and Replace dialog box
2. Check to be sure the find which field is empty
3. Click Options
4. Click the Format button to open the Find Format, and specify the formatting to find
5. In the Find Format, choose the Alignment tab and check the Merged Cells
6. Click Ok
7. In Find and Replace, Click Find All

Excel displays a list of merged cells in the worksheet. Click an address in the list, and merged cell is activated.

• Transforming data without formulas:

If you want to increase the values for all or some numbers by some range say for instance 3 percent, you can:

1. Activate any empty cell and enter 1.03. You multiply the values by this number, which would result in an increase of three percent.
2. Press “CTRL” + “C’ to copy that cell.
3. Select the range to be transformed
4. Choose HomeàClipboard–>PasteàPaste Special to display dialog box
5. In the box, Click the multiply option
6. Click Ok
7. Press Escape to cancel copy mode.

• Creating charts

Select the data of the charts that have to be created:

• Press the F11 key on the keyboard
• The chart uses the defaults. The chart we get is the Column Chart.

• Date Functions

Excel provides many functions to process day to day task quickly

• Today(): Returns the current date
• Now(): Returns both time and date.
• Click on Formatà Cells in the menus to bring up the Format Cells box
• Select the time category windows
• Pick the required format in Type window

---

Courtesy: udemy.com