Microsoft Excel

Wouldn’t it be great to be able to quickly apply a simple outline border to a cell or range in Excel without using the toolbar?  Let’s have a go, we will apply borders to each of the cells B6 through to B11 in the example below…

Read more ...

You may often need to calculate future dates in Excel based on dates that you already have.  For example, you may need to know what an invoice due date will be in 30 days but not including weekends.  Find out how, by using the WORKDAY.INTL function...

Read more ...

Did you know that instead of using the Insert Chart ribbon, there are two keyboard shortcuts for fast chart creation?  You can use F11 to create a full page sized chart or ALT F1 to create a chart within your worksheet...

Read more ...

Last time we looked at combining data from separate columns into one single cell, this time what if you have your data arranged in a single cell and you need to split it into individual columns?  Excel makes it easy, you can use the Text to Columns feature...

Read more ...

Sometimes data that you are working with isn’t always laid out as you need it to be.  You may have information in separate columns, that you need to combine into one cell.  In this example we will show you how to do just that...

Read more ...

If your data is not arranged the way you would like it, you can use the Transpose feature to swap data from displaying down a column to being arranged over rows or vice versa...

Read more ...

Sometimes you want to quickly format all the columns in your data into equal widths.  You can do this by selecting all the columns and altering just one column to the preferred width and this will change all the other selected columns:

Read more ...

One of the common pieces of data that you may need to input into an Excel sheet on a regular basis is the current Date or Time.  Did you know that there is a quick keyboard method for entering both of these?... 

Read more ...

You may know about the wrap text tool in Excel that allows you to arrange text in a cell over several lines, but did you know that you can achieve the same result using a keyboard combination as you type the headings?... 

Read more ...

You can use Conditional Formatting to make data stand out in your Excel spreadsheets. Find out how cells can be formatted to change the text style, add a border or even fill the cell with colour...

Read more ...

Speed up your formulae creation and add the calculation symbols (= / * + – ) to your Quick Access Toolbar. This will act as a memory aid if you only create formulae infrequently, as the symbols are always on display...

Read more ...

Have you ever wanted to monitor the rise and falls of your data graphically, but don’t want to include a full graph on your worksheet? In Excel 2010, 2013 & 2016 you can create tiny charts in cells using the Sparklines feature...

Read more ...

This is a real time saver if you are using someone else's spreadsheet and you want to see the formula behind the cell content.  Instead of clicking into each cell and viewing the formula in the formula bar, you can use a shortcut key or menu command to show and hide formulas on the worksheet.

Read more ...

So, you want to create a row or column of sequential dates, but you don’t want to include weekends.  Do you painstakingly go through the long list deleting the Saturday and Sunday dates?  No, let AutoFill do it for you…

Read more ...

When printing data in Excel, by default it aligns the data from the top left hand corner of the page.  If you would like to print your data in the centre of the page, here's how to set it up:

Read more ...

You have hundreds if not thousands of data entries in an Excel spreadsheet column, but how can you create a separate list showing only the unique entries and not all the duplicates?

Read more ...

Let's say that you have four separate Excel workbooks, or 2 or more worksheets in the same workbook that you wish to view at the same time.

Read more ...

If you wish to change the way the date looks in an Excel spreadsheet, you can change the format with a few simple steps.

Read more ...

The Fill Handle tool provides you with a quick way to enter a series of data into cells, such as months of the year and days of the week. But, did you know that you can create your own custom lists to use with the Fill Handle?

Read more ...

Do you find yourself frustrated by having to repeatedly click between two or more sheets in the same workbook?  Would you like to be able to view them all at the same time without copying and pasting into a new spreadsheet? Well here’s how you can do that quickly and easily…

Read more ...

Have you ever wondered why when typing in a number into an Excel cell it sometimes displays the number as a date? Well usually this is because a date format has been applied to the cell(s) at some point previously. Here's how to quickly fix the problem...

Read more ...

When printing a spreadsheet, Excel will not by default print the gridlines that you see on the Excel screen. Rather than having to apply numerous borders to the sheet, try this much simpler way of viewing and printing gridlines...

Read more ...

When importing or copying data into an Excel spreadsheet, you may find on occasions that the text is displayed in the wrong case i.e. UPPERCASE.

Within Word, it is quite straight forward to change the case automatically (with the word highlighted press Shift + F3) however, in Excel there is a little more to it.

Here’s how you can write a formula to automatically change the case rather than painstakingly retyping all the data manually…

Read more ...

You may already be aware of the Autofill function where you can hover your mouse over the bottom right corner of a cell (your mouse changes to a thin black cross + ) and click and drag to copy data or formulas into the cells below.

Read more ...

Here are a few useful shortcut keys that can be used when formatting numbers in Microsoft Excel:

Read more ...

Should you need to insert a list of numbers on a spreadsheet i.e 1 to 100, try out the following shortcut instead of having to manually type in each number:

Read more ...

When a cell reference within a formula needs to an absolute cell reference, you may be aware that dollar signs need to be included within the formula. Rather than having to manually type the $ in each time, try this quick and handy shortcut....

Read more ...

Let’s say you have a list of products and you want to know how many entries there are of a certain product...
Using the COUNTIF function will automatically calculate the answer for you:

Read more ...

When entering data into an Access Table or Query or an Excel worksheet, try out this shortcut for copying data from the row above:

Read more ...

A lot of people find the Data Validation List feature useful, as it allows you to have a drop down option to choose the required entry from.  But what if the list of entries is to grow?

Here is an example of where a Data Validation List has been used on column E:

Read more ...