How To Use Excel Spreadsheet – 5 Tips To Excel Nirvana

Hands down, MS Excel is the world’s most popular spreadsheet program. It has powerful features and wonderful shortcuts that can make short work of any business reporting requirements, household financial budgets and or product costing analysis. Unfortunately, however, not many people can brag that they know even 50% of the features that the latest MS Excel spreadsheet program has to offer. This discussion aims to explain some of the most powerful features of this spreadsheet program and in the process make it more usable for some of you. If you are a beginner looking for some guidance into the finer elements of Excel, then this discussion is definitely for you. Additionally, you may also check out this wonderful beginner course on MS Excel that we have.

Tip 1 – Creating a Chart from a Data Series and Tweaking It

Excel’s menu and toolbar have been replaced by the Ribbon. The Ribbon offers a more intuitive and user-friendly approach to providing all the necessary tools that a user may need while working on a spreadsheet. Charts are one of the most used features of Excel. Sadly, however, not many do know how to tweak them and or make the most out of them. A simple chart can be created by selecting a data series and then clicking on the chart icon from the Insert Ribbon. When you create a chart another set of Ribbons will open up to give you additional options. These are Chart Tools – Design and Format. You can use a different chart style, lay around with the colors, use a font that matches with your personality, edit the data range and do a hundred different other thing as per your need. There’s a lot more that you can do with charts. Check this Excel 2010 Intermediate course we have for more advanced tips.

Tip 2 – Using Formulas – Sum

Excel is often used for the purpose of budgeting and or costing breakdown. It requires an extensive use of formulas. Using formulas in Excel is easy. If you have no idea how to work with formulas on Excel then check this beginner course we have. Let’s say you have a data series listing the monthly bills to be paid in June. You want to know the total for the month. Of course one way of checking the total is by previewing. If you select the data range the total will show at the footer in Excel 2013. To make a sum of all the entries simply double-click on the cell where you need the total to appear, and type “ =sum( “. Now, select the first cell of the data range and while holding the “Shift” key down keep hitting the down (or up arrow key as per your selection) all the way to the last cell of the data range you want to add-up. When you are done type ”)” to complete the formula and then press “Enter”.

One thing about Excel is there are more than one way of accomplishing specific tasks. The above could have been also achieved by simply selecting the first cell with your mouse after typing “ = “ (at what point it will become highlighted in a blinking light effect) and then dragging the cursor to select the remaining cells. To conclude simply press “Enter”.

Tip 3 – Handling a Complicated Formula

Using this methodology you can create complicated arithmetical formulas. Just remember the simple acronym for the order that we studied back in school – BODMAS (Bracket, Of, Division, Multiplication, Addition and Subtraction).

Say you want to create a complicated formula in a single cell. Let’s say you want to find how much you spent on a three day trip to the beach. You spent $200 a night for 3 nights for the room, 3 breakfasts @ $20 each for two person, 3 lunch @ $75 for two and 3 dinner for two @ $85. Return cab fare $165 and incidentals another $125. The simple formula to enter is “(200*3)+(3*20*2)+(3*75*3)+(3*2*85)+165+125”. Enter this in a cell after typing “=” and it will calculate the total cost in a jiffy.

Tip 4 – Creating a Live Link

Let’s say you are working on a cost calculation sheet. You have separate raw materials sheet, manpower / labor sheet which has details of salaries and wages, fixed and variable components and you have a sheet to calculate overheads. You need a way to bring all the data together.  To seamlessly create a live link between one cell on a worksheet and another cell on a separate worksheet in the same document or a separate document follow these steps. Type “=” on the cell where you want to create the live link and then click the cell with which you wish to link it. Press “Enter”. This will create a live link. Any values that you enter in the second cell will automatically be copied on to the first cell. To add hyperlinks and bookmarks you can review this external link.

Tip 5 – Sorting and Filtering

Sorting and Filtering are two very useful tools in Excel. The sorting tool allow you to arrange a data range alphabetically or using any custom parameter that you set. Let’s say you have two columns of data. One lists the names of fruits – Apples, Mangoes, Bananas, Oranges, Guavas, Pineapples and the other their hypothetical prices 60, 50, 30, 50, 40, 80. Let’s say you want to sort this list alphabetically. Select the column named “fruits” and click on “Sort & Filter” in the Home Ribbon. You will be given option to sort either alphabetically or using a custom parameter. After you have selected an option you will be prompted whether you want to expand the selection. This will happen only when there is data in the adjacent column. Click yes and the data is sorted according to your preference.

There may be need to filter the data to find out specific information. Let’s say that you have a long list of data which lists the names of employees and how much they have withdrawn as conveyance advances over the course of the month. If you want to find out how much a specific employee has withdrawn you will need to filter the list. To filter a list select the column headers and click on “Sort & Filter” in the Home Ribbon. Click on “Filter”. A drop down icon is now visible on the column headers. If you select any one of them you can further select a parameter to filter the list. Let’s say you select A from the Name column. The list will be filtered to show only the withdrawing done by A during the month.

Excel has more wonderful advanced tools. For a deeper understanding of them check this Advanced Excel for Professionals course we have.