I love what I do; I get to work with an outstanding team to help hundreds of people with their challenges. I’ve learnt a lot from the teams I’ve worked with, no matter the size or industry we all have challenges to overcome, difficult customers, creating a budget or keeping a project on track.
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 […]
Here’s how to view two Excel 2010 workbooks at the same time, side by side, a very useful tip when you want to compare data without having to go back and forth! 1) Open both Excel workbooks 2) Select Window then select Compare Side by Side with (Spreadsheet 2) 3) When you have finished, […]
When you have an Excel Worksheet with masses of data, it’s not going to be easy to sift through it. So, in order to view sections of data, you can use the filter tool. Select the cells you want to filter (no need to select the column headers), then click the Home tab on the […]
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 […]
On a recent Excel VBA Intermediate course everyone thought a macro for converting text to upper or proper case would be helpful. It’s a good one to put into your Personal Macro Workbook then assign to a button on your Quick Access Toolbar. Here’s how to do it: 1. Starting with a blank Workbook record a macro called ConvertCase in the Personal […]
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 […]
If you have an Excel Worksheet that contains Data relating to the hours of training for each Employee for Microsoft Excel Training London on Monday, Tuesday and Wednesday, you may want to return the hours trained for a particular employee on a particular day in another Excel Workbook or Excel Worksheet. The spreadsheet shown below is the […]
If you want to add more than one row to an Excel Worksheet, drag select the number or rows you want added to the spreadsheet. Then right click on these selected rows, choose Insert from the menu, and the new rows will be added above the rows you first selected.
If you want to add a row to an Excel spreadsheet, these are the simple steps you should take: With your mouse, right click on the row header below where you want the new row to be added. Then, click Insert. Follow exactly the same steps if you want to add a column […]
If you don’t want part of the Excel worksheet to be visible or when you don’t want certain data to appear in print outs, then a simple solution is to temporarily hide columns and rows. Hide a single column: 1) Right click on the column header of the column you want to hide (this is […]