Data changes result in recalculations of totals which are not always visible, depending on your spreadsheet layout. To keep an eye on how these totals are being affected without dashing around your spreadsheet, collect the important totals into a Watch Window. You can then see the collective effect of your new data from wherever you […]
Whilst everyone is changing from the old 2003 version of Excel to the new 2010 version, some people will still need to provide spreadsheets in the older version. If this is for a single or occasional file, in the Save As dialog box simply change the File Type in the bottom box to Excel 97-2000 […]
AutoCorrect entries simply replace a text error or shortcut with the correct or longer version. For example, typing “teh” will automatically correct to “the”. This feature has a long list of entries which you may wish to change, remove or add to. Select the File tab, Options and go to the Proofing tools page. Click […]
There are many built in lists in Excel, numerical, date/time, text and numerical, and some text entries for month and day (try typing Jan in a cell and replicate the sequence down with the bottom right corner of the cell). For sequences that are not immediately available, Excel gives you the option of creating your […]
To enter the equation editor, click on the Insert tab and select the equation button on the far right. This brings up the Equation Tools ribbon with many preset symbols and options. The equation is entered in the highlighted text box on the worksheet. Mathematicians, have fun!
For larger spreadsheets, Range Names are more convenient than scroll bars. To name an individual cell or block of cells highlight the cell or cells first, click on the Name Box, type in an appropriate name, and press the Enter key. (Range names do not accept spaces.) To find the block again, use the drop-down […]
There are times when you inherit a spreadsheet and wonder how it works. You can identify the cells with formulae, but still have difficulty tracing the sources or subsequent calculations. This is where Excel’s Auditing Tools can help point you in the right direction (literally). Tracing Precedents and Dependents Select a cell with a formula. […]
In large tables or summaries in Excel 2010 it can be useful to be able to hide rows. In this guide we will run through how to hide rows. This is what you do: 1. Select the rows you want to hide. 2. Right click the selection. 3. Click Hide.
Let’s look at an example using a formula that provides a total amount. In the total row, we want to display the sum of all household expenditure. Make sure that the cell you want to contain the total is active and then click Home > Editing > AutoSum. Because the active cell is positioned directly […]
Microsoft Excel 2010 normally copies all the information in the range of cells you select when you paste the data. Use Excel’s Paste Special command to specify other options, such as pasting only the cell contents (without the formatting) or only the formatting (without the cell contents). To paste particular parts of a cell selection, […]
The following examples use the INDEX and MATCH worksheet functions in excel 2010 to find a value based on multiple criteria. Example 1: Data in Columns Method 1 Start Excel. Type the following data into a new worksheet: A1: Part B1: Code C1: Price D1: Find Part E1: Find Code A2: x B2: 11 C2: […]
If you want to make your Excel Worksheet appear a little less dull, or maybe you want to draw attention to a certain section of text, why not try rotating the text. And here’s how you can do it.. – Select the cell with the text in you would like to rotate. – Click the […]
Above is an example of a standard pivot table in Microsoft Excel 2010. It is set up with financial quaters as column headers and products as Row labels. I’m interested in seeing the results for the combined sales for the first half and the second half of the year. As you can see I have […]
Ever closed an Excel workbook and clicked the “Don’t Save” option, then realised you should have saved it! Microsoft Excel 2010 now has a file recovery option for just such an occassion. This has proved invaluable in our Microsoft Excel Training Courses in London. Always check the AutoRecover time in the Excel options. To do […]