Month: October 2016

Google Sheets Functions – TRANSPOSE

Here we’re going to look at how we can use the TRANSPOSE function to change our data from being vertical to being horizontal, or vice versa. Changing a single column or row of data Here we have the days of the week in a column and we want to put them horizontally, so that each day is in a different column across the page. In cell C1, I write the following function: As you can see, it changes the information from being vertically stacked to being horizontally laid out. This works both ways. If the original data was horizontally laid out, then in cell A1, we could write this function: This would display the days vertically. Converting 2 vertical columns to 2 horizontal ones Here we have the days of the week and some teachers. I want to lay both sets of information horizontally. This is easy, just include both columns in the range in the TRANSPOSE function. This will then take column A and put it in row 1, then take column B and put it in row 2. Converting multiple horizontal rows into vertical columns Similar to the previous example, we can do the same for more than 2 sets of data. Include the complete range in the TRANSPOSE function. And sure enough, it transposes the information from rows to columns. The above is using the TRANSPOSE...

Read More

Google Sheets Functions – PROPER, UPPER, LOWER, TRIM

In this post we’re going to look at how we change the format of text to suit our needs, using the functions PROPER, UPPER, LOWER, and TRIM. It’s particularly useful when working with text that has come from, for example, a form, a different computer system, or indeed when someone has typed in on your Sheet. This is because the capitalisation isn’t the way we want it and the text may contain unwanted spaces, which can cause problems on your Sheet. We’ll cover the following areas: Using the PROPER function to capitalize each word Using the UPPER function to capitalize all letters Using the LOWER function to put words into lowercase Using the PROPER and TRIM functions to clean up text Using ARRAYFORMULA to copy PROPER function to all rows Capitalizing only the first letter of a sentence and putting the rest in lowercase Changing a name to initials Example 1 – Using the PROPER function to capitalize each word The PROPER function capitalizes every word in the text, which is useful for correcting the format of names. Here we’ve got my name in various formats. As we can see the syntax of the PROPER function and indeed the UPPER, LOWER and TRIM functions is very simple, just add the text or cell reference in brackets. So I write the following in cell A2. Then I copy that down in cells B2...

Read More

Google Sheets Functions – FILTER

Here we’re going to look at the FILTER function. But you can add a filter to a table via the filter option in the menu, I hear you cry! You can, but the FILTER function allows you to put those filtered results on a different part of your page or on a different sheet, and thus, not affect the original table. It also allows for more complex filtering, which the filter option in the menu doesn’t offer. Here are some examples showing you the various uses of the FILTER function and how it can also be combined with other functions, to filter out the information you need, in one single formula. Example 1 – Filtering a list by a piece of text Here we have a list of companies and the products they sell. I want to a list of the ones only Widgets Ltd sell. I want to leave the original list untouched, so I put my FILTER function on a different part of the page, for example, in cell D2. First, I put the same headers as the original list (just by copying and pasting). Then in cell D2, I write the following FILTER function: This looks at the range A1:B12 (i.e. the data in the original table), then shows anything that matches the condition. In this case, it’s looking in column A (A1:A12) and shows anything that matches...

Read More

Google Sheets Functions – COUNTIF & SUMIF

In this post we’re going to look at how we can count things and add things up depending on certain conditions that we set. There are four functions we’ll look at here: COUNTIF, SUMIF, COUNTIFS, and SUMIFS. Here I’ll show you the various aspects of the functions through some practical examples. Example 1 – COUNTIF: Counting the number of instances of a specific number In this example, and to introduce COUNTIF, we have a list of part numbers of some products and we want to know how many parts we have that are numbered “123”. In cell B2, we write the following formula: There are two parts, first you state the range of values you want to look at, then in the second part, you state what you want to look for. So, in this case, we’re looking in cells A2 to A16 and we are looking for the number 123. As it looks down the row, every time it finds the number 123, it adds it to the count. So, in this example, it found 6 instances of the number 123. Example 2 – COUNTIF: Counting how many values are over a certain number We can also use COUNTIF to count how many values are greater than or less than a particular number. In fact, it can check against pretty much any type of mathematical condition, an example of...

Read More

Google Sheets (19) – Alternating colours in a table

When working with tables in a spreadsheet, we often want them to be easy to read and understand. To help us Sheets now has a quick little tool, which allows us to add alternating colours to the rows of our data, making them not just prettier but much easier to read, whilst being really quick to create. Here we have a plain table of data. First, highlight the area you want to format (usually the whole table). Go to the Format menu, then click “Alternating colours”. This will open the sidebar. Here from top to bottom you have: The range the formatting will apply to. Whether you want a different colour for the header of the data or the footer. A set of default colours to choose from. The option to create your own default colour combination, which is then saved for future use. At the bottom, you have the option to remove the formatting. When you first open the sidebar, by default it adds a grey and white format to your data. To change this, just select one of the default options. Here I’ve selected a purple combination. As you can see, it’s easier to read and looks better too. If you want to choose, your own colours, either select a default one and edit it, or click the plus button to add a new one. Then click...

Read More
  • 1
  • 2

Archives

eBooks available

Pin It on Pinterest