Excel is packed with lots of great tools. One of my favourites is the Subtotals. Here you can take a list, sort it into order so that appropriate items are grouped together, then click a button and it will determine the subtotal for each of those groups.
Not only will it create subtotals, it will do averages, max and min to find the highest and lowest values.
Check out the video tutorial below to see it in action. For more tutorials see my website www.jargonfreehelp.com.
HLOOKUP and VLOOKUP functions are very useful functions on their own but if you combine them together you can do even more. If you are using VLOOKUP rather than having the two columns one with the value to lookup and the other with the value to retrieve with the HLOOKUP you could get it to get values from more than one column.
In the example in the video below you can see how I used this to determine delivery charges from one to city to another using a table of charges based on the cities. The HLOOKUP determined which column the delivery was coming from and the VLOOKUP uses the row where it is going to. The HLOOKUP has the column number which the VLOOKUP uses for determining the column index number.
If you need to know about the VLOOKUP and HLOOKUP function you can see these on my website www.jargonfreehelp.com at the following links:
…with the free 12 days of Christmas app from iTunes.
Everyday from the 26th December for 12 days there is one free item. I have done this now for the past few years and although not everything is something I want I have got plenty of things I did. Download it today ready for tomorrow, the link to the App Store is https://itunes.apple.com/gb/app/12-days-of-gifts/id777716569?mt=8
As far as I know this works for the UK store but search your own country’s store to see if they have the same promotion.
Merry Christmas and enjoy the holidays.
Gary
So this week I’ve had a bit of inspiration creating some Excel tutorials but don’t worry I’ve also started others including the photography and video podcasts which will be with you in January 2014. But here is one Excel one that I use all the time.
It is the database functions in Excel. These functions can look at a list and by putting in some criteria you can use some functions to do things like total up, average, find the highest or lowest figure. For example, if you had a list of student grades you could find the average just of maths students or find the highest or lowest scores of science students.
In the example I have in the video below I use a list of computer models and how many in stock. Then I can use the DSUM function to total up the quantities of a particular brand. I also use it to find out the average cost using DAVERAGE and the highest and lowest costs using DMAX and DMIN.
This is one of those functions that I find needs a bit of practice if you are doing the ECDL advanced for Excel.
Don’t forget you can also find more on my website www.jargonfreehelp.com
Some time ago I did a tutorial on VLOOKUP and have finally got around to doing one on the HLOOKUP function. It is very similar except that the data is in columns as opposed to rows as you would find when using VLOOKUP.
HLOOKUP will take a value and find a match for it in a table and return the given corresponding value in one of the rows below it. This is useful for finding matching data or for finding a value based on a range. In this example I use it to find staff information based on a staff ID number. However, you can do far more including using it find a value within a range.
You can see a different type of example using student exam results and returning a grade in my VLOOKUP tutorial. Whatever you can do with a VLOOKUP you can do with an HLOOKUP, the difference is the way you have laid the table out. The advantage of a VLOOKUP is that the data is in rows and Excel has a lot more rows than columns, so if you have a lot of data you may want to consider the VLOOKUP.
See also my VLOOKUP function which you can find by clicking here.
Here is the HLOOKUP tutorial.
I’m often asked how to remove duplicate entries in an Excel list or database, they are the same thing but Microsoft have more recently called them lists. Thankfully it is easy to do, particularly on more recent versions of Excel. Not that it was difficult in older versions just different.
How many times have you got a list and knew that there were duplicates in there that needed to be removed or you wondered if there were duplicates? This can be any type of list, it might be one that you have for your shopping or a list of names for a mail merge, whatever it is you will want to get rid of those duplicates.
Here I show you how to do this on the more recent versions and how to do on the older versions.
So far I haven’t really discussed photography, one of the things I enjoy doing and something I now do regularly on a professional basis. My blog tends to be about technology and lets face it cameras and photography use a lot of technology. That is why I thought it would be a good idea to share some of the things I know about photography.
In many ways photography has become far more accessible and cheaper, ok so the cameras may not be that cheap but comparable to film cameras. Where you save is in the printing. Digital cameras also open the doors to experimenting and trying new things to see the results instantly.
I thought I would start out by discussing the aperture and how it affects the depth of field, that is how much is in focus in your photograph. An aperture that lets in a lot of light has a shallow depth of field meaning that whatever you focus on not much is in focus in front of it or behind it. If you have a small aperture then more is in focus in front and behind. Examples of this are portraits where you may just want the subject to be sharp and landscapes where you want everything to be in focus.
You can find out more in this video. More videos are on their way about exposure, shutter speed, ISO, filters and more.
How often do you get annoying calls or text messages? I seem to get them everyday from the same numbers trying to get me to upgrade my phone, tell me I can make a claim and how I can get money back from PPI (payment protection insurance).
For all the criticism that iOS 7 got for its design they did add in some useful functionality including the ability to block calls and text messages.
What I’ve been doing is asking them to call back and immediately blocking the contact so that they cannot get through. The only problem is it does not work if they have withheld the number. But I have managed to reduce it down.
This was also useful recently when on holiday knowing some people would still try and get in touch that I did not need to speak to, this can bump up call charges as you end up paying too, and quite a bit, if you happen to be abroad.
Here in this video I show you how to block contacts.
There are many things that Excel can do but fundamentally it is used for doing calculations. These can be simple or complex. I’ve done quite a few on functions and also some that use multiple functions but I needed to cover the basics, how to do simple calculations. So here it is getting started with formulas.
I’ve used a very simple spreadsheet to demonstrate how to use addition, subtraction, multiplication and division, I’ve also thrown in a quick example of using Autosum too.
Check out my website http://www.jargonfreehelp.com to see and introduction to functions that will build on the knowledge of formulas, you can also watch tutorials on specific functions. These will help you take control of Excel.
Searching for files is something I seem to do everyday, I think this has been made worse by the volume of photographs I now take, much of this because it has become a key part of my work. However, it is not just photos I am regularly searching for. Moving them all into one folder and categorising like that does not necessarily make sense and is not practical. With Mavericks there is a great new feature that allows you to tag files which means you can give them a set of key works that you can search for so easily.
Thankfully if you have a set of files that need the same tag in the same folder then you can do more than one at a time, that is certainly going to save me a lot of time as I can see I am going to spend a lot of time tagging up files.
Here is a video tutorial I made to help you out.
Gary Schwartz's Blog 