Join David Rivers for an in-depth discussion in this video Top 5 Excel shortcuts, part of Weekly Office Workshop.
In this week's workshop, we're going to explore my five favorite shortcuts when working with Microsoft Excel 2013. These are guaranteed to save you some time and effort when working with your spreadsheets here in Excel, so let's get to shortcut number one. And it's a keyboard shortcut. We're gonna use this exercise file called Excel Shortcuts, you'll notice a couple of tabs at the bottom. We're looking at sales by region to begin with. Now summing up values is probably the most used task in Excel, so this little keyboard shortcut's really gonna save us some time. To sum up a column of numbers, all we have to do is select the cell at the bottom of the columns, so let's go to cell B 11.
I'll just click there once and use the keyboard shortcut which is Alt and the Equal Sign. Hold down your Alt key, tap the Equal sign on your keyboard, and look what happens. The formula is about to be inserted for you. We didn't have to highlight any data, it found it, entered the formula for us, to accept it all we have to do is press Enter, and there it is. How easy is that? Now the next thing we're going to do is use another keyboard shortcut to see all the formulas in a workbook. Now this is a lot better than going around the spreadsheet, clicking in different cells looking for formulas.
If you want to see them all at once, use the keyboard shortcut Control and the Tilda, so hold down Control, the Tilda, you'll find it up in the top left corner of your keyboard, and look what happens. Instead of the values, we see the actual formulas that give us those values. Press it again, Control + Tilda, to go back. That's a nice little feature to save you some time when trying to find, or locate formulas in your worksheet. Alright, this next one is for creating charts. All we have to do is select the data cells we want, and we're gonna use a keyboard shortcut to quickly create a chart.
If we want to see what all of this looks like in a chart, all we have to do is click and drag over the areas. So we're going from D 3 all the way to H 8, so we're getting some labels in there, and we're also getting some values. Now we're gonna use the keyboard shortcut Alt + F1, so hold down Alt, press F1, and you can see what happens, a new chart is created for you with a default look and feel. But all of that can be changed, just go over to the formatting icon over on the right to change the chart's style, so if there's a different style that you like you can hover over them until you see one that you like.
I kind of like this one right here. And if you want to change the color scheme that's being used in that chart, just hover over some of the different color schemes to see what they look like. Kind of like this one right here, gonna select color three, and now all I have to do is click anywhere in the background to deselect. I might want to move this chart around, maybe even resize it, but you get the idea. Now there is another way to create charts. So if you don't like the default column chart that we see here, just click once to select the outside border, press delete on your keyboard to remove it, and let's look at another option using quick analysis, which does a lot more than create charts.
We'll select the same data, so click and drag from D 3 across and down to H 8, and this time you'll notice in the bottom right hand corner the quick analysis icon. There is a keyboard shortcut for this as well, it's Control Q. We'll give it a click, and you'll see different categories now. There's formatting, there's charts, totals, tables, spark lines. With formatting selected, all you have to do is hover over some of these options like data bars, and you get a visual representation of the data. It's a great way to compare the numbers that you see in that table.
Color scaling works as well, or icon sets, greater than, all of these values, of course, can be adjusted, and we can even clear formatting from here. Let's go to charts, because under charts we can choose different styles. Now this clustered bar, for example, when we hover over it, that's what that's going to look like. It's different than the column that we saw, which was a clustered column, the default. Move across to stack, then we can access more charts from here. Let's just go to the clustered column and give it a click.
And there's our nice looking chart, we'll just size it down, and move it off to the side. Alright, we'll deselect by clicking in the background, and that's a quick look at quick analysis. There's some other things we can do with this as well. Let's go back to that data and reselect it. We'll click the same icon, or use the keyboard shortcut Control + Q, and go to Totals, instead of Grand Totals we could get Sums for each of the columns. You can see Averages if you like that instead, or Counts, the numbers of items in each row, five for each of those.
Running Totals, Sums, etcetera, and those sums go in the right hand column as well. I'm gonna select that. Now there's already data here, do we want to replace it, we click Ok, and any of the old data is replaced with the new, easy as that, that's the quick analysis tool. Alright, let's look at one more now, but we'll go to a different tab. Let's go to our Sales by Product tab down here where we have a list of Home Product Items. You can see their codes. In there is the Department Code, so instead of manually typing in the codes, we can extract it from these items using a feature called Flash Fill.
And Excel is very smart in figuring out what needs to be extracted without us using Macros or special coding. So, for example, the first Department Code for our first Item here in row four is SP, probably sports. We'll type in SP, and press Return. Now in the next one, Excel's gonna look for a pattern here. We're gonna type in H W, actually all we need to do is type in the H, and you can see the W appears, Excel's pretty good at figuring this out, and the rest, you can see, are about to be filled in if we press Enter, just like that, that's the Flash Fill.
So think about all the scenarios where you have data embedded in other data that can be extracted using the Flash Fill feature. So those are my top five favorite shortcuts here in Excel 2013.