Explore in-depth tutorials and strategies for data management, analysis, and visualisation using Microsoft Excel.
The IPMT function allows you to work out the Interest payment based on the set criteria as outlined in the syntax below. Syntax IPMT(rate,per,nper,pv,fv,type) Rate is the interest rate per period. Per is the period for which you want to find the interest and must be in the range 1 to nper. Nper is the […]
PPMT Financial Function in Excel 2010 The PPMT function allows you to work out the principal payment based on the set criteria as outlined in the syntax below. Syntax PPMT (rate,per,nper,pv,fv,type) Rate is the interest rate per period. Per specifies the period and must be in the range 1 to nper. Nper is the total […]
Get the lastest currency rates automatically updated in your spreadsheet! And they use the data within charts, functions and more… Select the DATA tab on your excel 2010 ribbon and select EXISITING CONNECTIONS Select the option MSM MONEYCENTRAL INVESTOR CURRENCY RATES Its as easy as that! Give excel a few moment and you will have the […]
To add a background to your spreadsheet simply follow the following procedure; On your ribbon select the tab PAGE LAYOUT Click on the BACKGROUND button Select the image you wish to have as your background Its as simple as that.
Calculate the percentage if you know the total and amount For example, if you score 42 points correctly out of 50, what is the percentage of correct answers? Example The example may be easier to understand if you copy it to a blank worksheet. Create a blank workbook or worksheet. Select the example in the […]
A question that often arises when using AutoFilter with Excel is… how can I sum the visible data only? The solution to this question may not always be apparent but all is not lost as there are many Excel training courses in London that deal with how this can be achieved. There are 2 options […]
Microsoft Excel has the Named Ranges feature that allows users to give a name to a cell or range of cells that can make it easier to refer to in formulas. The naming of the cells can be tedious if there are numerous ranges to be named on the sheet. Here’s a quick way of […]
Excel has some great functions for “What if…” scenarios. One tool that can be used is the Excel Goal Seek. This allows you to choose any current formula and set the answer value to your own goal. e.g. If you have a formula that works out how much profit you make from selling items, such […]
Excel 2007 introduced the function COUNTIFS to count cells based on multiple criteria. For example suppose you wish to count how many times the client Fowler buys more than 250 shares. The answer for tha data below turns out to be 2 using the Countifs function entered in B13. Similarly for Owen the result turns out to be 1. […]
If you ever need to send an Excel workbook by email to someone else, you may want to hide certain worksheets. When they receive the file, you want to ensure they cannot unhide any hidden worksheets. After attending an excel training course you may only learn the skills of how to protect a worksheet with […]
Have you ever wanted to automatically number rows of data in Excel? One way to do this is by using the Row function. For example, type a heading such as ID into A1 then in A2 type the formula =Row()-1 In this example suppose you want the numbers to be inserted automatically as you add […]
We usually maintain the data in multiple worksheets to check the data for each instance and to view them individually but it would be great to merge all the data from different worksheets in Excel spreadsheet to analyze it. Consolidate feature in Excel 2010, lets you to pull-each record from the worksheet in to one […]
Slicers you will be able to depict the datasheet fields more comprehensively. Excel 2010 include this feature for the first time, which lets you to slice your data by showing you only the element stored in the tables. Real usage of Slicers can be seen with pivot table. Unlike Pivot table, which pivots down the […]
For those of you who like bulleted lists and are frustrated that you can’t insert one in a cell, there is a way to insert bullet points in a cell to emulate such a thing. Just keep in mind that this isn’t a true list, so you can’t generate more bulleted lines by inserting a […]
If the Watch Window toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.) is not displayed, on the Formulas tab, in the Formula Auditing group, click Watch Window. Select the cells that you want to remove. To select multiple cells, […]
Select the cells that you want to watch. To select all cells on a worksheet with formulas, on the Home tab, in the Editing group, click Find & Replace, click Go To Special, and then click Formulas. On the Formulas tab, in the Formula Auditing group, click Watch Window. Click Add Watch . Click Add. […]
When cells are not visible on a worksheet, you can watch those cells and their formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) in the Watch Window toolbar (toolbar: A bar with buttons […]
There are several ways to exchange data between Microsoft Office Access and Microsoft Office Excel. To bring data into Excel from Access, you can copy data from an Access datasheet and paste it into an Excel worksheet, connect to an Access database from an Excel worksheet, or export Access data into an Excel worksheet. To […]
You can use Excel 2007’s Subtotals feature to subtotal data in a sorted list. To subtotal a list, you first sort the list on the field for which you want the subtotals, and then you designate the field that contains the values you want to be summed — these don’t have to be the same […]
This technique can be used to calculate the difference between any two dates. The worked example shows calculating age and showing this in days and in whole years. It involves using the TODAY and TRUNCATE functions and nesting several functions together. Create a table for showing date of birth for required people – start this […]
You can display the weekday for dates stored in Excel. This is especially useful for checking if due dates fall during the working week. To do this you need to use the weekday function and custom formatting features of Excel. The screenshot below are from Excel 2010, but this work in earlier versions too. Ensure […]
Autofill in Excel can be used to repeat a custom sequence. Type in the entries for the sequence into adjacent cells Highlight the entries Drag down from the Autofill handle until you have the required number of entries
Autofill in Excel can be used to complete or extend any number sequence where the gap are equal. Type in the first two number of the sequence in two adjacent cells Highlight both entries Drag down from the autofill handle Drag until you reach the number of entries you require When you let go you […]
If you’re new to Excel, here’s a guide to the different mouse shapes you’ll see, and what they are used for. General Select What it looks like: Thick plus shape. When you see it: Appears when the mouse is anywhere over the worksheet grid. Why do I need it: Selecting a range of cells by […]
Autofill is a feature which will copy content down a column or accrss a row. However it also creates sequences, using the same technique, with things that it recognises as being part of a sequence. One such is dates. Enter the starting date of your sequence in Excel and ensure it is formatted as a […]
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. […]
Opening a file in Excel 2003 (Video)
The simplest way to adjust the width of the column is to simply drag it from the corners. Additionally it can also be done by going to the Home menu and Format > Column Width option.
In many cases, you don’t need any particular column, here are the steps to remove any column(s) from your Excel worksheet. Select the columns, right click and choose Delete.
In the column header, select the number of columns that you wish to add to your excel sheet, if you want to add 2 columns then select two columns, right click and choose Insert, you will find 2 new columns added to your spreadsheet.
Right-click the column, adjacent to which you wish to add another column, and choose Insert, a new column will be inserted to the left of the selected column.
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.
One of the most asked questions during our Excel 2010 Training Courses has to be the subject of calculating with dates. Excel stores dates (and times) as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day (e.g. 31/10/2011 10:00 is stored as 40847.42).This is called a serial date, or serial […]
Merge Cells in Excel 2010; Study the spreadsheet below: If you look at Row 1, you’ll see that the “Shopping Bill” heading stretches across three cells. This is not three separate cells, with a colour change for each individual cell. The A1, B1 and C1 cells were merged. To merge cells, do the following. Type […]
This article describes the formula syntax and usage of the AND function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel. Description Returns […]
The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in […]
The COUNTA function counts the number of cells that are not empty in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.). Syntax COUNTA(value1, [value2], …) The COUNTA function syntax has the following arguments (argument: A value that provides information to an action, an […]
Countif Counts the number of cells within a range that meet the given criteria. Syntax COUNTIF(range,criteria) Range is the range of cells from which you want to count cells. Criteria is the criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted. For example, criteria can be expressed […]
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, […]
Here I have a table with employees work hours spread over a 7 day week. The employees are on the rows and the days of the week in the columns. I have created two criteria cells, one for the employee and one for the days. I then created another cell which is where I will enter […]
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: […]
There are two different ways to add commands to the quick access toolbar. The first way involves using the Customize Quick Access Toolbar button, to the right of the toolbar. When we click that, we get this menu: In this panel, we can see that the commands that already appear in the toolbar have a […]
Here’s a brief tip on how to instantly brighten up your Excel spreadsheet with very little effort. By changing the colours of the tabs, your Excel Worksheet will change from being dull and boring to exciting and colourful! It will also help in organization, being particularly useful when organizing all your Excel Worksheets relating to […]