Using Microsoft Excel

Like the rest of the Microsoft Office, Excel tends to be a lifesaver when it comes to important projects, presentations and other work related stuff you have to achieve. From merging sheets and calculating complicated formulas to combining information and simple mathematics operations, Excel can do it all, and quite easily when you master it. And that is not hard at all. Read on to learn how to properly use Excel.

How to use Excel

If you are using the program for the first time, first you must learn the very basics. These include creating a new spreadsheet, executing basic math in it, writing and formatting column text, navigating auto-fill features, adding and removing columns or rows, and keeping everything visible and clear.

Once you have all of these simple things on your side, you will have to dive down a bit deeper.

source: oakstraining.com

Excel tips to try out

1. Pivot Tables

This is used to reorganize your data in a spreadsheet. They do not change values, only sum them up and compare. To create a pivot table, go to Data>Pivot Table. Excel will do the rest, but you can always change it. You have four options:

Report Filter, that allows you to look at only some rows,

Column Tables, to use as the headers,

Row Labels, to use similarly as the last one,

Value, which allows you to sum, average, max, min, and count your data.

2. Add more than one Row or Column

As you fill out your table, you might require more than one new row or column. To do this, highlight the number of preexisting rows or columns, right-click, and select “Insert” from the menu. This will give you as many new fields as you need.

3. Filters

With quite large data sets, you do not need all of them at all times, and you only want to look at some of it. This is where filters are important. Click the Data tab and select “Filter”. You can choose to ascend or descending order, and which rows or columns you want to have shown.

4. Remove Duplicates

Large sets of data sometimes have duplicate content, which can be removed. This is quite handy. To do this, highlight what you want to remove, go to Data, select “Remove Duplicates” from Tools, and a pop-up will appear. Confirm the action and you are golden.

5. Transpose

If you have low rows of data, you may wish to transform these items into the column, or vice versa perhaps. Highlight the desired row or column, right-click, and select “Copy”. Then select the cells where you want to copy, right-click again, and “Paste Special”. You will receive a module, where you should click to transpose. Check the box and then OK.

6. Text to Column

If you want to split the info into two different cells, go to Data and select “Text to Columns”. In the module, select “Delimited” or “Fixed Width”, depending on what you need. The first will break up the column based on commas, spaces, etc., while the second allows you to specify the location of the split. Excel will show you a preview when you select what you want, click “Next”, and then “Finish”.

7. Simple Calculations

To use basic math, use +, -, *, and / to add, subtract, multiply, and divide. Parenthesis are also available. To average, use =AVERAGE(cell range). To sum up, a column, use the formula =SUM(sell range).

8. Conditional Formatting Formula

This allows you to change color based on the info in the cell. So if you need to mark numbers in the top 20% of the data, you can. Highlight the cells, choose “Conditional Formatting” from Hone, and pick what you need. A window will show asking for additional info. When you are done, click “OK” and it should appear immediately.

source: support.office.com

9. IF Statement

When you need to see if there is a corresponding cell to something you put in, you can use this formula. It is IF(logical_test, value_if_true, value of false).

Logical_Test is the IF part.

Value_if_True is what we want the program to show if the value is true.

Value-if_False is what will be shown it is not true.

This can be used to for example show only something that has a value of more than a 100.

10. Dollar Signs

These do not represent American currency in Excel but make sure that the exact column and row are the same even if you copy the same formula in neighboring rows. When you want some values to stay the same no matter where you use dollar signs. For example, instead of (=A5+C5), you type (=$A$5+$C$5).

There are many other complex things excel can do, including VLOOKUP Function, Index Match, and COUNTIF Function, that require more time to master.