In a recent blog post, I described the process required to combine multiple sheets into one single table using PowerQuery. Shortly after posting this, the Power Query guru Wyn Hopkins pointed out an alternative method that makes the process more efficient which I wasn’t aware of. Note - If you've not checked out Wyn's PowerQuery content you're missing out, check out his YouTube Channel here and his podcast 'Power Query Magic' which is available on all good podcast platforms. When connecting to multiple sources in PowerQuery, you'll often be presented with the option to combine files. The Content column below displays the combine icon which consists of two down arrows and a single line across the bottom of the button. Clicking the 'combine files' button presents the 'Combine Files' window which provides a number of options. At first glance, it might seem that combining both sheets in the example below is not possible as there is no way of tickin
Posts
A recent discussion on R/Excel " What is the most efficient way to insert a similar chart in several pre-existing worksheets of a workbook while making sure that the data source of the chart refers to the worksheet where it has been inserted?" initiated a conversation about the different methods of displaying data from multiple sheets which i ncluded a question around working with data on multiple sheets that needed to be summarised on a chart that was identical on each sheet. As with most things with Excel, there are a number of methods that would achieve this result, I believe the quickest and most efficient is to use Power Query to combine the data and then display the output as a pivot table/chart with a slicer to allow the user to select each source as required. I realise this isn't providing a separate sheet for each data source however, assuming there are no restrictions on the visibility of the data, this is a 1-page solution that contains all the required data
When working with text in Excel its often useful to combine the text from two or more cells into a single cell. This can be done with a couple of different methods Method 1 - using the Concatenate function I've previously blogged about the basics of this function in this post . Here we'll look at the function in a bit more detail and some common use cases. Almost all people I've helped with Excel aren't familiar with the word 'Concatenate', its definition is "link (things) together in a chain or series." which is exactly what the concatenate function does. Its simplest use case would be, = Concatenate (A1, A2) This would combine the values in A1 and A2 into a single value which would be displayed in the cell in which the function is placed, lets look at a real example Here we have a list of first names and last names and we'd like to combine them into another column titled 'Full Name'. We'll begin by using the Concatenate function and
When presented with a table of data that requires analysis, one common requirement is to calculate a running total or what some might call a cumulative sum of individual values. One application might be a table containing the amount of money spent across a number of transactions and there is a need to provide a running total to show the total spend across the data set. There are a couple of different methods to achieve this but the easiest way is to use the function =sum($B$2:B2) where column B contains the values that you wish to add in a running total. This gif (below) is the above function being added to a table containing values. I've used the keyboard shortcut F4 to set the cell reference to absolute so that when it fills down, the second part of the cell range increments with each row.
The HOUR function in Excel returns a whole number between zero and 23 as an indication of the hour associated with a time value. Zero is the period from 12am - 1am and then increases in whole numbers through to the period from 11pm to 12am which will return 23. The HOUR function "Returns the hour as a number from 0 (12:00AM) to 23 (11:00PM)" The function only requires one argument, a cell or an input that includes a time element. This could be of the format dd/mm/yy hh:mm or just a time reference of hh:mm. In this example, I've created a list of random times which I'll then combine with the HOUR function to return an interfere number that indicates the time associated with the input. The formula is simply =Hour(D2) which I've then filled down to assess the time associated with each value in the range. As you can see from the example, the output of the function is an integer number that varies with each of the times in the source cells.
The COUNTIF function provides the ability to establish how many occurrences of a specific value or condition exist in a range of values. This function begins to increase capability with a function that is simple to operate. COUNTIF "Counts the number of cells within a range that meet the given condition" There are two arguments required to use the COUNTIF function, the first, range, is usually a group of cells, the second, criteria, is the argument that is used to match the cells in the range. In this example, we'll use the COUNTIF function to establish how many time the word "day" appears in a list of text. The range of cells that need to be checked for the word "day" exist in the range B2:B28 and the criteria we wish to count is entered as "day". The result of this function is the following: So we can conclude there are 7 occurrences of the word "day" in the range B2:B38. We can also use a cell reference as the second argument as