Consolidating many Excel sheets into one master sheet

Consolidating many Excel sheets into one master sheet

A while back we had a situation where we had collected a set of information from various sources and wanted to get all of it into the same Excel Worksheet and I was asked if I could help. There were about 60 spreadsheets all with the same columns but with a variable number of rows and of course, different data. The requester had linked all 60-odd spreadsheets to a master Workbook and wanted to know how to get the information from each transferred to a single master sheet that he could share as a consolidated list. This process needed to be repeatable as the authors of the 60-odd different worksheets

Convert or flip data between rows and columns in Excel

Convert or flip data between rows and columns in Excel

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 vice-versa is because I expect it to be on the ribbon as a commonly used function but it is not.

Applying conditions to AVERAGE, SUM and COUNT functions in Microsoft Excel

Applying conditions to AVERAGE, SUM and COUNT functions in Microsoft Excel

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 remember it was on my list of articles to write about Excel but I’d obviously not written it yet. Anyway,

Using conditional formatting and functions in Microsoft Excel

Using conditional formatting and functions in Microsoft Excel

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 a very simple example. I have a spreadsheet with some (fictional) purchases I made a few years back. Column A

Manipulating and formatting text using functions in Microsoft Excel

Manipulating and formatting text using functions in Microsoft Excel

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 function as text, character, string or values and show them as text or other values in the examples but they

Using dates and date functions in Microsoft Excel

Using dates and date functions in Microsoft Excel

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 will cover how dates are stored, their format and then go through the current date functions. I have excluded time

Finding a value in a range using Microsoft Excel functions

Finding a value in a range using Microsoft Excel functions

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 SUMPRODUCT. Excel finding function descriptions INDEX(array of values, row number, column number) The INDEX function allows you to find a

Using cell reference names and named ranges in Microsoft Excel

Using cell reference names and named ranges in Microsoft Excel

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 do about it? Using the naming feature within Excel you can make your life (and those of reviewers) a lot