Excel

Last year, I started writing an article on using Excel for a football league to work out the positions in the league table based on the results. I never finished it but then during the current pandemic and the uncertainty over completing the current football seasons I started playing with Excel again. So, this is not really a technology article, although in the Excel examples, I have used some useful Excel functions. This is also not an important article considering the global crisis surrounding the COVID-19 pandemic. Those of us

One of the things that I have to do periodically and always forget how to do is converting rows of data to columns or columns of data to rows. This quite often occurs when I’m collecting data from different sources and want to put them all in the same format in Microsoft Excel. From the 2007 version of MS Excel (I think), there has been a neat feature to enable you to do this. The reason that I always forget how to convert from columns of data to rows and

This is my first article on Microsoft Excel in over two years and continues the series with descriptions and examples of applying conditions to the popular Excel SUM, COUNT and AVERAGE functions. I started writing this article about 6 months ago when one of my friends asked me how to count the number of occurrences in a spreadsheet where a condition in another column was true. I had thought that I had already written such an article and was about to point her to it but it wasn’t there. I

This is another one of my articles about Microsoft Excel covering conditional formatting and conditional statement functions such as TRUE, FALSE, NOT, IF, AND, OR, XOR, IFNA and IFERROR. Conditional formatting allows you to display your workbook cells differently based on specified criteria (the values of cells). Conditional functions allow you to present results in your cells based on different criteria. So one is for the display conditions and the other for the values returned based on a conditional statement. Conditional formatting We will start with conditional formatting and with

In this short article I run through some examples of using Excel’s text functions to manipulate or format data. This follows on from my other Excel related articles. Quite often I use Excel to change data to the format that is required in a database and text functions come in very handy in all sorts of situations. I also use Excel to format data into HTML code for use in web pages and I’ll give an example of that. In the descriptions below I refer to the values within the

Following on from my article about using names for cells or a range of cells, I have decided to continue writing about Excel and today’s subject are Excel’s use of dates and its date functions. As I am in the UK the default way to show dates in the screenshots will be the UK format but that is just something to be aware of rather than any issue as following through with the examples on your own machine will display in default format for your location. In this article we

Following on from my other Microsoft Excel articles, this one is devoted to looking up information from within a range of values such as a table of data but using the functions that Excel provides. We will look at four functions used for finding operations within Excel; VLOOKUP, HLOOKUP, MATCH and INDEX. First we will have an explanation of each and then walk through some examples of their usage. Finally in this article I will explain how to look up a value in a table based on multiple criteria using

Everyone who uses Excel and that has written a formula (you might think that must be everyone but there are some people who just use Excel for sorting rows of data) will have used the row and column reference at some point (i.e. A1 or R1C1) or a cell range (A1:C2 or R1C1:R3C2). Anyone that has gone back to their workbook after a long time to make modifications or anyone that has to modify or decipher someone else’s workbook will curse those row and column references. So what can you