excel tips and tricks 2Excel 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.

Learn how to work with Excel and your business at Udemy.com.

Tips and Tricks

Formulas

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:

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

Learn how to work with Excel and save time with a class at Udemy.com.

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.

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

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.

  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.

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.

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.

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

Excel provides many functions to process day to day task quickly

Master Excel shortcuts and tasks with a course at Udemy.com

Top courses in Excel

Microsoft Excel - Data Visualization, Excel Charts & Graphs
Maven Analytics, Chris Dutton
4.6 (13,490)
Bestseller
Use Excel Like A Pro. Fast.
Kieran Luke
4.6 (990)
Excel Macros and VBA: Automate Your Excel Workload
Think Forward Online Training
4.6 (439)
Microsoft Excel - Excel from Beginner to Advanced
Kyle Pew, Office Newb LLC
4.6 (152,788)
Bestseller
The Ultimate Excel Programmer Course
Daniel Strong
4.4 (12,960)
Bestseller
Microsoft Excel - Data Analysis with Excel Pivot Tables
Maven Analytics, Chris Dutton
4.6 (19,354)
Bestseller

More Excel Courses

Excel students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo