#### Archive

Category Archives for "Excel Lessons"

## Selecting Next Cells In A Different Order

If you have ever used Excel, you probably know already that you can move from your current cell to the one to the right, or below by using the tab or return keys.  So what if you want  to select the next cell, but the next cell is not in the normal order? For example, […]

## Multiplication Table Challenge Solution

The Challenge In the last blog post I set a challenge for you. Create a table similar to the one below, in cell B2, create a formula that can be copied to all other cells in the table.  The result of this will be a multiplication table.   The Solution The solution in theory is […]

## Multiplication Table Challenge

Feeling up to a challenge? Here is a challenge for you to complete in Excel. Can you come up with a formula in cell B2 below that can be copied down and across the rest of the table below? From cell B1 across to K1, list the numbers 1 to 10 From cell A2 down […]

## Create A Planner In Excel

It’s that time of year again when I start receiving requests for help with creating planners for the new year. These planners are usually for teams or groups to organise holiday schedules and rosters. This year I created a reusable planner that can be used in future years and also includes most of the features […]

## 3D Sum

In Excel, a 3D Reference refers to the same cell or range in multiple worksheets.  The 3D Sum allows you to sum the same cells in multiple worksheets. To demonstrate this,  we have 4 worksheets and in Cell B1 of each sheet, there is a sales value that we want to get the total of. […]

## UPPER, lower & Proper in Excel

In an earlier post I discussed using len, left and right and mid.  Now I will look at more functions that fall under “Text” in Excel – upper, lower and proper. These 3 functions allow you to change the case of text in your files. I usually use these functions on files that have been […]

## Sort Left To Right In Excel

If you’ve covered even the basics of Excel, there is a good chance that at some point you have sorted at least 1 column of data in ascending or descending order. Sorting up and down is well covered in classes, books, tutorials and with a big button in Excel ribbons that says “Sort”, but […]

## Office Design Tip

I’m often asked for help with, or to recommend a good (cheap) program for designing certain shapes like banners etc. There are many good ways to do this out there, feel free to look into some yourself, this is what I usually tell people, but I also give them an Office design tip. A […]

## How To Add Macros To Custom Toolbar

Having trained a variety people and helped others with macros, especially in Excel, I have noticed a common issue – assigning keyboard shortcuts to the most regularly used macros. Ctrl along with most of the letters are already assigned as shortcuts, by assigning for example Ctrl and B to your macro, this overwrites the […]

## A Quicker Way To Create VLookUps Across Multiple Columns

Recently I have been asked by a few people for a quicker way to create VLookUps across multiple columns. In each case the user wanted a VLookUp to pull in several columns of data, but did not want to have to create a VLookUp for each column (one case would have been over 20 columns […]

It is well known and well publicized that graphs and charts can be used to demonstrate and present data visually to an audience.  The visual of the data can highlight points of data quicker and easier than just numbers in a table. One thing that I have noticed over the years is that when talking […]

## Paste Special

Paste Special is another time saving trick that may be going underused. You are probably already familiar with the cut, copy and paste functions in a lot of applications. Copy allows you to make a copy of selected or highlighted data to be pasted somewhere else Cut allows you to remove selected or highlighted data […]

## Excel Pivot Tables

In Excel, Pivot Tables  are a quick and easy way to summarize, analyse and present data.  The speed that the tables can be put together and rearranged makes them an invaluable and important tool for analysis and decision making. The more data you have to analyse, the more obvious the uses of Pivot Tables are. […]

## Excel VBA Macro Demo

Starting from this post we have covered a range of areas relating to Excel VBA Macros. From the feedback received, it seems that people can see the benefits and uses of macros, but are still wary of them! As long as you record sections, breakdown code, comment it well, and have a search engine available […]

## Excel VBA : Working With Multiple Files

During the last few posts we have been building on our knowledge of Excel VBA Macros.  This time we will use what we have already seen, but now we are going to work with more than one file. The Goal From this post, we will create a Billing file with a macro. This macro will […]

Follow

Get every new post delivered to your Inbox

Join other followers: