Microsoft Excel Tips & Tricks

WindowsIf you have programs (e.g. Internet Explorer, Word, Excel) pinned to your TaskBar, hold the Windows Logo key and press 1 to start the first program, Windows Logo + 2 for the second and so on.ExcelStruggling to understand a complex formula? Select the cell containing the formula and click "Evaluate Formula" on the Formulas tab. This will show you the steps Excel follows to arrive at the final result.WordDo you need a non-numbered paragraph in the middle of a numbered list? Type the whole n....

Working with numbers comes with a few rules and one such rule that deals with numbers in decimals is to round them off. If you work with Excel, you know that it doesn’t round off numbers unless they’re being treated as currency. If you’re dealing with other types of numbers i.e. non-currency ones, and need to round off numbers to certain decimal point, there’s a pretty simple way to do it.       Open the MS Excel file you’re working with and sele....

Need to format some cells quickly? Select the cells and right-click on them – as well as the Shortcut Menu you will see the Mini Toolbar which allows you to make many common formatting changes without having to find the buttons on the Ribbon. In Word and PowerPoint the Mini Toolbar appears automatically when you highlight some text.

Want some help checking yourdata entry accuracy? Try “Speak cells on Enter”, which reads back what you havejust typed when you press Enter, or “Speak Cells”, which reads out the selectedcells. (You will need to add these two buttons to the Quick Access Toolbar fromthe “All Commands” list in the “Quick Acccess Toolbar” section of the ExcelOptions).

When you use AutoFill (the small black blob at the bottom right corner of the active cell) to copy data, look for the Auto fill Options button that appears when you finish dragging. It allows you to control and change what the AutoFill does.

Say you have column headings:Pink            Blue               Greenthat you want to change into row headings:PinkBlueGreenMoving the data manually, one item at a time, would be a tedious process. Tosave time, you can use the Paste Special command to quickly transposea column of data to a row of data, or vice versa.To transpose rows and columns:              &nb....

When you highlight more than onecell containing a number, Excel displays the Average, Count and Sum of theselected cells at the bottom right of the Status Bar. If you are using Excel2013, you also get a “Quick Analysis” button at the bottom right of theselected range that makes it easy to add Totals or create Charts.

Do you have a Workbook with so many Sheets you can’t see them all? Drag the small vertical bar that is just to the left of the horizontal scroll bar to make more room for the Tabs Or right-click the little arrows to the left of the Sheet Tabs to see a list of all the Sheets.

Easily the best new feature in Excel 2013, Flash Fill solves one of the most frustrating problems of Excel: pulling needed pieces of information from a concatenated cell. When you’re working in a column with names in “Last, First” format, for example, you historically had to either type everything out manually or create an often-complicated workaround. Flash Fill can automatically add data formatted the way you want without using formulas.In Excel 2013, you can now just type the first name of th....

Do you ever need to enter dates of all working days in your spreadsheet? Try entering a date into a cell, and then use the autofill handle to fill down or across. Initially, Excel will include all dates, including weekends, but you can change this by using the Autofill Options button. This button appears to the right of the last filled cell. Click on the Autofill Options button and select the Fill Weekdays option.

Click the “Show formulas” button in the Formula Auditing group on the Formulas Tab to see the formulas instead of their results in all cells. Click on a cell containing a formula to see the cells that it uses colour coded. Click the “Show Formulas” button again to turn it off.

Want to add more data to an existing chart? Check that your new data is the same size and shape as either a row or column of the existing data (including the heading) - it doesn't have to be on the same sheet as the Chart or the rest of the data. Then just Copy the data, click on the Chart to activate it and click Paste.

Have you ever typed in a set of figures and then wanted to check them? Why not get Excel to read them back to you using the “Speak Cells” features? These are on the “Text to Speech” toolbar in Excel 2003, or you can customise the Quick Access Toolbar in Excel 2007 or later – look for “Speak Cells” in the list of “Commands not in the Ribbon”

When you are dealing with a spreadsheet that you didn’t create yourself, it can be useful to locate the cells that hold formulas, particularly if you are checking for errors. Normally, you only see the formula contained in a cell when you select that cell: it appears in the Formula Bar. On Excel 2007 and 2010, to show all the formulas, you can select the Formulas tab and click onto the Show Formulas button, which is in the Formula Auditing group. On Excel 2003, select Tools from the menu b....

Excel has shortcut keys for practically everything but it’s hard to remember them all.A few useful ones are: CTRL + 'copies the data from the cell above CTRL + ; inserts today’s date (as a fixed value) CTRL+SHIFT+$ formats the cell as currency CTRL+SHIFT+% formats the cell as a percentageCTRL+SHIFT++ (CTRL and plus)inserts cells or rows (depending on what you have selected) CTRL+- (CTRL and minus)deletes cells or rows (depending on what you have selected)

Don’t just insert a blank sheet and copy and paste the information you need… copy the whole Worksheet by dragging the sheet tab to where you want the new sheet to be and holding the CTRL key while you release the mouse button. Then you can adjust the new sheet as you wish. This way, everything about the existing Worksheet is copied, including column widths, filter settings, page setup, headers and footers and so on. Oh, and by the way, you can even drag the Worksheet to a different ....