0

In many cases we need to combine information from  multiple sources together. Using the Vlookup function is one way to do this (if we just want to pull in matching information) but sometimes we actually need to add information together e.g. from branches of the same company. One way to do this is by combining our files together. However I want to show you one extra trick with this. If this is something you regularly do, you can create a view of the multiple workbooks and then save this as what is known as a workspace. Then instead of trying to remember what files you had open, you can then just open the workspace.

Check out the video below

You can download the three files here as well to practise on: February January March

As always if you have any suggestions for what you would like me to do a blog post on. Please let me know.

 

 

Continue Reading

0

In this tutorial I plan to show you how to create a cover page/table of contents for your Excel sheets. i.e. how to create hyperlinks to jump to another worksheet in your workbook.

I’m going to look at two options. In the first one – we are just going to use text and in the second we are going to create a shape and create  a hyperlink from that.

 

here is the file for you to practise on: Hyperlinks_demo

You can also quickly remove the hyperlink by right clicking on the text or shape and choosing  Remove Hyperlink

 

Also if you are in the mood to upgrade your skills – check out some of the resources here. 

Continue Reading

0

I was recently doing some training and one of the issues that came up was that the user had the following problem:

Two stock lists (identical items), two physical locations and he needed to get a summary that showed the total number of items. I first thought about subtotals but when I actually tried it, realised it wouldn’t work. So instead I went via my beloved pivot tables…..

Here is the video and the instructions are below…Now unfortunately for some reason the sound quality recording was so poor that I ended up deciding to record a er, silent movie. (Alas, it can’t really be compared with “The Artist” or “The Gold Rush” ………) but the instructions are also given below…

Here is the file you can use to practise on:  Excel_remove_duplicates_file

Steps to sum duplicates

  1. Create list as normal
    1. If you think you will be adding to this list regularly take this step:

i.    Click on list

ii.    Choose Insert-Table

iii.    On the new ribbon which will appear, click on Summarize with Pivot Table

iv.    After this the steps will be the same as outlined in the rest of the instructions

  1. Convert to Pivot table (Insert- Pivot Table)
  2. Choose the default settings – i.e. New Worksheet
Values Quantity on Hand
Row Headings Number
Row Headings Description
  1. Make sure you are clicked in the pivot table
  2. Click on Design – Report Layout – Show in Tabular Form (this gets everything to fit across the page)
  3. Choose Subtotals – Do not show subtotals (this removes the headings)
  4. Then go to Options – Options – Options
  5. Click Display
  6. Clear the Show/Expand Collapse buttons (this removes the – from the left of the No. Description)
  7. To convert the Unit Cost to currency, right click on the Sum of Unit Cost in the pivot table
  8. Choose Value Field Settings – Number Format – Currency

Continue Reading