Ultimate Excel Course #10 - Formatting your Data Ranges as Tables for Superior Management | Alan Murray | Skillshare

Ultimate Excel Course #10 - Formatting your Data Ranges as Tables for Superior Management

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
6 Lessons (28m)
    • 1. Introduction

    • 2. Formatting a Data Range as a Table

    • 3. Exploring the Benefits to using Tables

    • 4. Creating your own Table Styles

    • 5. Formulas with a Tables Structured References

    • 6. Convert a Table back to a Normal Range


About This Class


This class is #10 in a series called the Ultimate Excel Course.

In this class you will learn how to work with tables to manage your data better.

Tables were introduced with Excel 2007, but are still unknown by many Excel users. However this feature of Excel gives access to very powerful features.

We will explore the benefits of managing your data with tables in this class. There are also more advantages coming in later classes in this course.

By using tables your data is dynamic, consistently formatted and wonderfully structured. Some of the greater benefits become clear when we write formulas based on table data. The references are more meaningful and easier to write when compared with normal grid references.

The Excel files used during the class are provided so that you can follow along. You can then take on the project exercises to test your skills in action.

Please post any questions in the discussion area and I will get back to you.

Grab a coffee, enrol and let's do this.

Please note that this is class #10 in a series called the Ultimate Excel Course.

You can find direct links to the other classes in the series below.

Class #1 - Excel Formulas Made Easy - Get up to Speed with Excel Formulas Fast

Class #2 - Essential Skills for Working with Large Spreadsheets

Class #3 - Logical Functions - The Decision Making Formulas of Excel

Class #4 - Conditional Formatting: Make your Data Come to Life

Class #5 - Lookup Functions - The Powerful VLOOKUP Function and Beyond

Class #6 - Validating and Protecting Excel Data

Class #7 - Advanced Formulas for Analysing Data

Class #8 - Excel Charts for Simple and Effective Data Presentation

Class #9 - Cleaning and Preparing Data Ready for Analysis

Class #10 - Formatting your data ranges as Tables for superior management

Class #11 - Master PivotTables for Powerful Analysis and Reporting

Class #12 - Get & Transform Data with Power Query

Class #13 - Introduction to the Data Model and PowerPivot


1. Introduction: Hello and welcome to Class Number 10 off the automatic sale course where we will look at format in your data ranges as tables for superior data management. On this is a feature of Excel that many Excel users are not familiar with or not truly familiar with, how useful they are. So we will start this class but understanding some of the key benefits to use in tables. Why would you use a table instead of a normal cell range, such as a 12 e 10? And what's the difference? We would then be, actually format in a range as a table because we can't really demonstrate these benefits or learn how to use them unless they're data's in a table. And that's very quick and simple to do. There's multiple ways as well what would then be creating their own styles. So most users will be quite happy with the exist in styles that supplied of excel. But you can create your own as well, and he may find that useful or even necessary. In some businesses, we were then right four meters because this is an Excel class on where Excel Class does not have formulas but we will be using them with structured references. We'll be covering some formulas that we've already covered in this course. But using the references to the table instead of to the sales on a spreadsheet known as a one style can agreed references. So instead of using sale references like the Free and E 10 will be referencing the name of the tay born and names of the columns, or even affair into a row. Is this road instead of having to worry about the row number, roller or the column letter, and then we'll look at format in our tables back to ranges. So hopefully, when you see some of the benefits on offer, you won't feel or see a need to do that. But it's worth well being aware of it, especially when you communicated in the a team of other users who may not be so familiar yet with tables that are very easy to convert back into ranges and then vice versa. So grab a coffee, what you're waiting for in role now on. I will see you in the class 2. Formatting a Data Range as a Table: hello and welcome to this lesson where we look at format in your data ranges as tables and why you would want to do that. So I've got this large list on screen off sows data, which is currently distort on the spread. She just a Xeni. Other data has bean on this course so far, and that is fine. I can conditional format that I can write my formulas, my vehicle cups and even do pivot tables. A riffing on this data range, However, to excel, it is literally just a bunch of cells. There's no meaning or understand into it. And to repeat myself to an extent that doesn't need to bay is not bad. That this is the case, but by format in this range of cells is a table opens up a lot more doors, especially in more modern versions of Excel. It can improve the way that we write formulas and read them. It can automatically update our reporting tools and have formulas and the things that we've got. Relying on this data source, it can allow us to easily format them. Andan used a powerful features such as power query and power, Piven Data modelling all from these tables, which we can't do it all, or, in some cases, as easily with this cell range. Now, to put this data into a table, you would simply just click somewhere in the range of it like I have done. And then I conniver stay with my home tab because you can see a format as table button sitting right next to conditional formatting a little over half way on that ribbon. I could also click on insert and you'll see the table button next to pivot tables. And if I hover over, it tells me the keyboard shortcut is controlled. T. So there's multiple ways off getting this data into a table. Mark Soft have made it very easy for us, and it's probably a nice clue as to how useful this feature is. Now. I'm going to use the home tab for this example for a click on former as Table. I have a range of formats to choose from. We're going to look at creating our own one in a lesson shortly, but I can quite happy choose one of these. And normally I choose this orange one in my demonstration like this orange medium 21. Apparently, I can give that left click. If your cell range already has some formatted elements, you might find it useful to right mouse. Click it because you can apply in clear. You can clear current formatting and apply this tables. I don't really have any formatting, only the fact that my head is a bold, so I could probably get away with whatever, although it's probably nice practice to do the right click and clear. If I write my abs quick and applying clear, it would double check my data range with me. Just wants to know of it. Sell a one to sell k 2156 and I can confirm that that is correct. If it wasn't, we may after highlight a range or type in at the road number or the column letter to make sure that data is correct and does your tail will have headers. Let's look at Roe. Yes, indeed, it does So quite happy with that click OK, on our data is in a table as easy as that 3. Exploring the Benefits to using Tables: Now let's look at some off the benefits off, spoken about off using tables, so the first thing you're going to notice is the formatting, and that alone is enough for a lot of people. I get asked very often in my training how to create this alternate color in arose on in years and years gone by. It was a lot harder than it is here. We used to use conditional formatting and techniques to do this or Matt Crows, but now we can simply put it in a table when it's done upon the designs habit a top. There is a setting for Banded Rose, and if we uncheck that, it disables it on. If we check it, it turns it back on again, and we're going to talk about creating their own styles soon. She can use different colors rather than the great has been used here, if that's important or different funnels. But that's normally a big advantages Banded row to readability off the data, and he's a requested feature table. Do that automatically at the top of God I feel too tall on. So yes, we don't need a table for fuel total. We've covered in this course already, but it's done. It's another thing. You don't necessarily have to do yourself once again on the design tab. We could disable the filter if necessary. But who doesn't feel toe a large list like this? So very, very useful. We have these styles gallery and the far right, so I'm able to change this to a different style with I think of made a bad twists and I want something different. But I'm quite happy with this orange one on the far left off the designs have we have the opportunity to name it, and that is definitely something we should be doing. It already has a name, but its table one Not really a very good name, so I'm going to call it sows because that is what this data is. It is Selves, and I'm present to confirm, and we now have a named table. So when we write her formulas in a later lesson, weaken, simply refer to this range as sounds. I don't care how many roses got or what sheet it's on or anything like this. It's just sows, and we'll see that shortly now. Another benefit is that if I scroll down this list because it's quite a large list, it will look like Row One disappears, and that's because it does. But the headers are in the column area. So instead of the column Headers, ABC, etcetera. We now have the names off the columns, the table names so we don't need Teoh. Freeze our headers. When your data is in a table, it does it for you, and it's probably even a little bit better because it's not taken up any space on their screen. So especially if you work with quite small screen on your or your laptop. Your Net book tablet. Then it may sound a little bit petty, but just No. 12 free road has been saved on your screen. Is that little bit more space for your for your work puts the filter up there as well. So a very nice tall is also in. Clever enough to know if I click outside the table, refer to the missus spreadsheet headers. We don't really care at headers Beavercreek. Inside the table, it comes alive and refers to the table columns. It's a very, very neat, so you probably noticing that the's tables they came out in 2007. But away Exhale version turned seven have been around for a long, long time at a little over 10 years, a time off, recording this lesson. So many people do not know about them yet. I don't know why that is, but it's slowly beginning to build in popularity and understanding on marks off the kind of feed in Europe way with some of the features they have as well. But when they came out, they were kind of viewed as this all in one solution. It does your formatting for you so you don't have to do it with format sales and former painter. It freezes your headers. It puts the filter on the head a row for you. She now to go data filter, and it kind of combined a lot off the functionalities that would otherwise be jumping around on different tabs at a ribbon to find and to utilize. Now, one of the most appreciated things with a table is that if I was to scroll to the bottom off this list, and if I imagine that we have just made a new sow, so I click in cell a 2157 just outside my table. Put in your I D 123 on a soon as I tab across or clicking the other sale. The table expands so immediately that new row is included in table. So any features or formulas that are used in this data are now automatically updating. To use these additional rose on that is huge. So all their conditional formatting their charts. Everything just automatically updates at it in sync with this constantly expanding dynamic table. It's a really common question I get, especially on beginners. Excel training on the first start, learning how to write formulas and referencing sales are a two to a 10. And they'll ask, what happens if you type of cell in 11? That's the formula automatically expand, and the answer is no. So we have to use clever workarounds and insert rose in the middle or add up entire columns rather than being precise and saying are just ease because we know if additional sales get added, it's not going to use them. So we normally highlight more than we need to kind of counter that classic exhale behavior . But if your date is in a table. We don't need to do that anymore. And did you notice how clever it waas that when I selected the column Header had just stopped on that road? Fancy A. It was smart enough to know there's nothing down here. Don't bother looking there because of the table. It's a really good sign of its extra understanding off. What's going on now. If that doesn't happen for any reason, if it doesn't automatically update to pick up, Reduced are included. Rose. We have to buy. Zoom in on it, this little blue angle thing in a corner there in just click and drag to update the table so hopefully won't need that too much because these tables automatically adjust. But the good news is, if we do need it, if it doesn't work for whatever reason, that maybe it's very simple to do similar to how a spoke about of charts and changing their ranges in the previous lesson, I could just click and drag that the table gets updated and anything using the table is now using the most up to date row is a central area to control how all other things reference that data source 4. Creating your own Table Styles: Okay, so we have seen how we can apply these table styles, and we have quite a few options up in the gallery. But you can also create your own styles. So let's imagine that we work for a company that have a standard style that they want to use. They have a color scheme with some specific RGB values. So I'm going to come in here and click new table style, and it will probably for a name for the style. And I'm just going to call it company Standard on. Then we conform. At off the table are all the different elements off a table style. So if I pick on the head a row to begin with, so just like him, there's the header. Is this different color? But we have a very specific light coral color that our company uses. So I will click on format the field tab, more colors and in custom, so that I can enter my own RGB values, which finished example, are 240 12 white and then one to wait again on this I'm imagining is a color for this fictional company that we use our brand identity and is very important and people have to use it. I'm also while I'm here, going to a player border so quite like when their table, that thick black border under the header, but no others. So I'm going to choose this one and just apply at the bottom of the sales and nowhere else , Then click. OK, so that my head arose formatted, and I turned it bold to illustrate that. Now the first row stripe is the other one. I want to demonstrate in this example, so select that one click format back in to fill on once again. I've got some RGB values to type some just moving into the area for it. And this is just 19 to 19212 which is a silver color click. OK, click okay or not not doing anything else, and you can see the preview on the right hand side. How the first row stripe is colored in gray on the 2nd 1 is the default white. I could to second row and use a different color there of my own choosing we can for my first last column. I'm imagining that you know, we never use it and at work. So I'm not interested. But we could do if you had totals in the last column or Head is in the first. You can put totals in the total rose well, one for Mike that differently, or even do things the whole table, such as borders. But I'm quite happy with these simple changes, simple but important changes we can also set. This is the default table style when this document not going to worry about that now, but I think that kind of speaks for itself. I Shorts Creek okay, and that style was created. So when I click on the more button to expand my style's gallery there it is at the top on one curricular button, and that style was in without specific color scheme, which is this silver. And it's kind of light coral color. And I had the specific RGB values to get that exactly correct and not just pick one off their reds or one of their oranges, which are not quite what we need. If I need to modify that in the future, I can just right mouse click and choose to offer removed early or to modify that style come in here and change color or do something different. And now that we've got this nice, consistent way, I can apply that towards tables in this spreadsheet and it is both quick. It's simple and it is consistent and also excels. Gonna now do that formatting for me as well. So not add more rows and columns, it's automatically going to apply those colors and that alternate banded rose really, really useful. 5. Formulas with a Tables Structured References: Now let's look at right in formulas using data in a table and see how it changes things. So let's begin. We've created a calculated column on the end of our table here. So if I zoom in on this table on, if we go for cell to here, we're going to create a new formula and to keep things simple. I just want to write a formula to subtract 10% of the total South value. Let's just use that as an example. So if I was to top equals on, then click on that sell Sell J two. It does not right. Still J two, it writes at Total Sours Value. So it references the current row and then the name off that field slash column total sales value. I can then multiply it by nor benign by 90% as normal. Or do any formula you wish to do on when I press enter, it automatically updates every cell within that table. So there were some key differences there, and you see how it's populated all the formatting as well, like we spoke about previously. But the key differences really are the structured reference. It references the name of the field instead off Cell J two. Just to quickly mention I can still do sell J two. That works absolutely fine, but you know that's not really the way you're probably going to use it Now. You see, if you look at these sales, that's doing J eight now, there's nothing wrong with the A one style grid referencing but by reference net sell. When using the structured references, especially more complicated formulas than this, they should be more meaningful and easier to read. We can see the name of the column instead of column j. An incident references. This sale was well, so they should have greater meaning and more durable was well to change is if people are to reorder or toe, insert or delete columns. We know this is total South value, and it looked day. If people rename the header as well, the fact we automatically updated arrested a table is great keeps it consistent. It knows that if you're doing it for one that you'll want to do it for the others as well saves you copy in that cell down order are quick ways obviously of doing that I can and just label this head up. If it was something that I wanted to keep, you could just put minus 10% or something and, uh, resize it, and easy is that the job is done. Now let's insert a new sheet a bottom and do an example of a formula similar to at some lessons. You know, a few lessons ago in this course we're going to do a summit function. Let me quickly correct scenario, a country and a total. Let me turn those headers bold, and I'll put the name of a country in such as Franz. Then we just format that still ready, so nothing quickly. But here we go. We've got the country. Where are you got a country? And there's a bunch countries, one of them fronts. And then we've got the total sours value that we saw in the previous example, and we're going to do some if to some or the numbers from that country. So they began across this sheet and we'll type equal some if open bracket so that it prompts us for a range criteria and some range. Now the range is going to be the list of countries on that table, but instead off clicking on this sheet tab down the bottom here instead of taking that approach. What I'm going to do instead is type sows, and you see how it knows that I'm referencing that table or asks me. I've convoy written it now, but I could double click that table to finish it off. There is a slightly larger or more awkward name, so instead of the sheep name, have got a table name. I can then put an open square bracket that you may remember seeing in the structured reference in the previous example, and it lists is that at this road it lists or the columns from that table the fields. I can just double click or type country close square bracket. So reference in the country column off the table could cells instead of column D on a sheet called a table was a fitness is called. It isn't date, so that has greater meaning. It has its more durable order, a aforementioned at benefits off this, especially if you work in with workbooks that have got many sheets and maybe some other ships A similar. They share the same kind of data sunis on poor cells may be the different stores. Maybe I have one in Manchester one in Cambridge, one in London when inborn if they've all got the same columns. But a family could just be Cambridge country. 1 may not country example at Manchester. Whatever march, maybe South on it keeps that consistency in the ease of reading Robin remembering letters and she names continuing with this comma, I referenced the cell with France, written in comma. I write sowles Open square bracket, where the numbers there in total sows value. Let me zoom out. At this point, it's get a bit crazy. Close square bracket closed, normal bracket press enter the total from France. But if I was to use him different, like Germany or island, then we're all good. And that's how it looks. And like I've said a few times, there are huge benefits to tables. I'm encouraging the use of them trying to showcase how good they are. However, we could have achieved this and have achieved it in this course, using normal grid references. It's just weighing up the pros and cons of each approach, and probably the greatest benefit of these is how meaningful where they are and how consistent they're going to look across multiple sheets of a workbook if you work with many sheets. 6. Convert a Table back to a Normal Range: Okay, so tables are fantastic. There's Mawr benefits to come in this course were being start to talk about data modelling and power query. But if you did not want your data in a table anymore, we could just click on design at the top, and it is a button to convert to range now. Take typically before you convert to range. You might wish to come and remove maybe the style. It's not necessary. But if it was to convert that back to a normal range, it would prompt me if I'm sure want to do this and I can say yes, but it maintains its format, and maybe that's a good thing. Maybe that's you like that. But if he didn't then or would probably remove the style from it, first of all, and there's a nice none style here and then convert to range, and it's as though nothing happened on we can go back to using their normal grid references , and you can see how the formula in cell L two over here has gone back to use in the sheet name cell reference. We know we don't necessarily need that dollar sign or that sheet name right now, we can go and tidy that up. But the fact is automatically converted Those structured references to something that would be useful. Now, if that table was no longer there is a pretty good