Sorting (and beyond!)
By Bob Umlas, Excel MVP
In this blog, I’ll discuss sorting, likely beyond features you knew were available! We’ll start with this screenshot of a file:
It needs sorting. There are many ways to sort data.
There are 3 ways to access sorting from the Data tab:
1– 
2–You can right-mouse click on a cell and select Sort:
and this leads to another flyout with more options:
3–Then there are the sort options from the Home tab, way over on the right side (and includes options for filtering, at the bottom half):
Let’s look at these different options.
The A-Z from the Data tab will sort the contiguous range of cells based on the active cell. So, if I wanted to sort the original screenshot by the first column, Country, all I need do is click in any one cell in that column and click the A-Z tool (or select the entire contiguous range of cells), and the beginning of the data would look like this:
Had I clicked the Z-A button, it’d look like this (I’ve scrolled down a bit so you could see then change in column A):
You can see that row 143 has a different country, UK, and that’s being sorted in reverse order alphabetically, after the US. Canada shows up in row 238:
(By the way, sorting is case insensitive, but there’s an option to be case sensitive which I’ll show later).
OK, here’s the first trick I’m going to assume you didn’t know: If you wanted to sort the region within the country, you could simply click one cell somewhere in column B, click the A-Z tool (on the Data tab), click in column A, click the A-Z tool, and you get this:
The trick is to click in the reverse sequence of the order you want the columns sorted. Here, we wanted column A to be the major sort, then within column A, we want column B sorted. So, we click a cell in column B, A-Z tool, a cell in column A, A-Z tool. I’ll do it again, clicking a cell in column D then C then B then A, each time clicking the A-Z tool:
Cool?
OK, the next icon in the Data tab is also the main sorting tool access:
.
This brings up the following dialog:
Here are the various values:
The first option is to sort by Country, Region, Company, etc. Excel reads the headers in the first row. How did it know the first row was headers? Because of that checkbox you see at the top right, “My data has headers.” If this is unchecked, you would see this dropdown instead:
.
OK, so if you wanted to sort Region within Company, you would select Country as the major sort, then you click on the Add level button at the top, then select Region from the dropdown:
You could continue this process for all the columns, and if you make a mistake you could click the Delete Level button. The Copy Level does just that – copies the level which is the active one. To see which level is “active” you can see the slight grey background behind it. In the above illustration, Region is the active level. You can use it as a starting point to then only change one item. For example, in the following dialog, the first level was copied to produce the second one, and now all that needs changing is the color in the Order dropdown. This is slightly easier than re-entering all the fields.
You can sort one level A to Z, and another Z to A, etc.
The third dropdown had an option for Custom List. Why choose a custom list to sort by? Well, if you have month names as text (as opposed to real dates formatted as text), then if I sort Jan thru Dec, I’d see this:
Alphabetical, but not very useful! So, you would do this:
Then the result is what you’d hope for:
Let’s take a closer look at the Sort On the dropdown, usually “Values”. As you may have noticed, you can also sort on Cell Color, Font Color, or Cell Icon.
Here’s the dialog for sorting cell color in column B and then font color in column D:
The Order column in the dialog shows all the various color possibilities that exist in the column:
Region:
Department:
Let’s sort the yellow region (cell color) then a red department (font color):
Or how about Yellow region followed by Brown region:
(Notice Region is there twice to accomplish this sort. Also, notice I could choose “On top” or “On Bottom”):
One more, then we’ll move on!
Same as above, but alphabetical for each as well, and then numerically, descending:
Which yields:
The sort by cell Icon is for when you used any symbols from the icon sets of conditional formatting. A quick example where I used conditional formatting of 3 icon sets in column E:
Now I can sort by the icon set:
Here’s the result after scrolling down a bit:
Okay. One more button we haven’t looked at, the Options button at the top. This brings up:
See? I told you we’d show you how to do a case-sensitive sort! There’s the checkbox at the top to do that.
Usually, you sort top to bottom, but you can sort left to right. Suppose I want to reorder these columns so that I have Company, then Region then Department. First, insert a row at the top, and enter the sort sequence you want to see!
I also need to select all the cells (pressing Ctrl/A) will do it) then bring up the sort left to right feature shown above from the Options button, and I’m presented with this dialog:
The sort by dropdown shows Row 1, Row 2, etc. I want to sort row 1 left to right (blanks sort to the bottom in a top-to-bottom sort or to the right in a left-to-right sort. Here’s the result:
Now I can delete row 1 and the columns are sorted as I wanted.
Last tip. Double spacing data. Or triple-spacing, or more!
Enter the value 1 in a parallel range in an empty column, and select the blank cell to the right as well:
Now double-click the fill handle (dark square in the bottom right corner of the selection):
Press Ctrl/c (copy), then ctrl/down arrow (takes you to last value), then down arrow (takes you to the empty cell below the last value), then ctrl/V (paste), and here’s the bottom of the data I was using:
Now you have 2 rows with a 1, two with a 2, etc. Click on one of these cells in column F and click the A-Z tool and presto!:
It’s double spaced! The blank rows all got sorted into place. Clear column F and you’re all set!
You can do this left-to-right as well. Here is a sequence of screenshots as a sample to insert 2 columns between each column (then we’re done!):
Copy thru 1 thru 5 and paste twice:
Bring up the left-to-right sort options then choose to sort on row 1:
Then delete row 1 and you’re done!!
Happy sorting!
About the Author
Bob Umlas has been using Excel since its inception in 1986. He has had more than 300 articles published on subjects ranging from beginner to advanced, VBA, tips, shortcuts, and general techniques using virtually all aspects of Excel. He is the longest-running Microsoft Excel MVP in the world (25 years). He has been the technical editor of (about 20) of Bill Jelen’s (MrExcel) Excel books. Bob speaks at Excel conferences around the world on his favorite topic, Tips & Tricks, which wows even the experts. He is the author of 3 Excel books and is the current leader of NYC’s Excel Special Interest Group (which meets on the 2nd Tuesday of each month).
Books:
This isn’t Excel, it’s Magic!
Excel Outside the Box
More Excel Outside the Box
How to Hide and Show Rows and Columns in Excel
By Bob Umlas, Excel MVP
In this blog we’ll explore the various ways to hide and show rows and columns. The bulk of the discussion will address columns, but it applies to rows as well (unless otherwise noted).
You can change a column width by dragging the column separator (the little line between columns) left or right:
In the following figure, the column separator between D and E is clicked (notice the cursor shape), the mouse is held down, and dragged to the right:
If you drag it to the left enough it will narrow or hide the column (when the width becomes 0 it’s hidden):
When you let go of the mouse there’s a double-line indicating that column D is missing (this shape varies with the version of Excel used):
but when headings are not shown (from the View tab you can turn them off), there’s no indication a column is missing. Here, it looks like cell D3 is selected, but in fact it’s E3 because column D is hidden:
Another way to hide the column is select any cell in the column, say D1, then on the Home tab, in the Cells group, there’s a Format dropdown which has access to the Column Width:
When you issue the command, you get a dialog with the current width, like this:
and you can type in any number from 0 to 255. When you type 0, the column is hidden.
You can also hide a column via Home/Format/Hide & Unhide/Hide Columns.
There’s also a shortcut to hide a column. Whichever column is selected will become hidden when you press ctrl/0 (a row will hide when you press ctrl/9).
All of the above applies to more than one column as well. If you select A1:E1 and use any of the methods, all 5 columns will hide.
Okay, how about unhiding? Again, there are several ways. It’s not as easy to select a hidden column, however! So you can type the cell address into the name box to get the cursor into the column. Or, you can press the F5 key (Go To), and type in the cell address. Here, D1 was entered into the name box:
and then cell D1 became selected. You can unhide this with the column width command we saw above, change the 0 which will be there to 8.43, or any number, etc. This is not literally unhiding – it’s resetting the column width to a number. Unhiding will restore the width to what it was before it was hidden! You can unhide it by Home/Format/Hide & Unhide/Unhide Columns:
Ctrl/shift/0 (ctrl/shift/9 to unhide rows) will do the job as well. You can also select the columns before and after the hidden column and unhide them (only the hidden one will be affected). Here, C thru E is selected:
And finally, you can carefully position the cursor a little to the right of the D-column separator (in this example) so it looks something like this:
then drag to the right which will reveal column D as well!
Lastly, suppose you have text like this:
And you want to widen column C so it all fits in it. You can drag the column separator between C and D to the right and visually adjust it, or you can double-click the column separator which does a “best-fit” for the longest cell in that column, or you can select a few cells (vertically or horizontally) or the entire column, and use Home/Format/AutoFit Column Width:
Enjoy!
If you are interested in bringing Excel MVP Bob Umlas into your company (either virtually or traditionally) to do Excel consulting and/or training work, contact roxi.nevin@iil.com.
About the Author
Bob Umlas has been using Excel since its inception in 1986. He has had more than 300 articles published on subjects ranging from beginner to advanced, VBA, tips, shortcuts, and general techniques using virtually all aspects of Excel. He is the longest running Microsoft Excel MVP in the world (25 years). He has been the technical editor of (about 20) of Bill Jelen’s (MrExcel) Excel books. Bob speaks at Excel conferences around the world on his favorite topic, Tips & Tricks, which wows even the experts. He is the author of 3 Excel books and is the current leader of NYC’s Excel Special Interest Group (which meets on the 2nd Tuesday of each month).
Books:
This isn’t Excel, it’s Magic!
Excel Outside the Box
More Excel Outside the Box
Excel Shortcuts
By Bob Umlas, Excel MVP
Excel has hundreds of keyboard shortcuts. In this blog, I’ll describe some of my favorites
Ctrl+Shift+ numbers 1 thru 7, and 0 (and the tilde key, ~) can be used for formatting. Let’s start with this sample worksheet, and assume A1 thru B2 is already selected and contains 12345:
Ctrl+shift+1 – add comma and 2 decimal positions:
Ctrl+shift+2 – Change to a time format:
Ctrl+Shift+3 – Change to a date format, but Day-Month-Year:
Why October 18? Excel stores dates counting the number of days since Jan 1, 1900 (that’s day #1). So, day #12,345 is October 18, 1933!
Ctrl+Shift+4 – Change to currency (Easy to remember because of the “$” on the 4):
Ctrl+Shift+5 – Percentage notation (Easy to remember because of the “%” on the 5):
Ctrl+Shift+6 – Change to scientific notation (Easy to remember because of the “^” on the 6, meaning raise to the power. That is, 3^2 is 3 squared, or 9):
Ctrl+Shift+~ (tilde – character to the left of the 1)- restore to General formatting:
Cttrl+Shift+7 – Put a border around the selection:
Ctrl+Shift-0 – Remove the border from the selection.
Ctrl+Shift-8 and Ctrl+Shift-9 don’t do anything regarding formatting.
Ctrl+U will underline, Ctrl+B will bold and Ctrl+I will italicize cells.
Non-formatting shortcuts:
Ctrl+minus will delete cells. If a whole row is selected (or more than one), it will be deleted. If a whole column is selected, (or more than one), it will be deleted. If less than a whole row or column is selected when you use Ctrl+minus, Excel will give this dialog:
Ctrl+Plus inserts cells, with the same “rules” as deleting cells. The dialog presented if not a whole row or column is selected is:
Ctrl+W will close a workbook (and will prompt for saving if not already saved).
Ctrl+N will create a new workbook.
Ctrl+O will prompt for opening a workbook.
Ctrl+P will print a worksheet.
A reminder that Ctrl+Z, Ctrl+X, and Ctrl+C are undo, cut, and copy.
Ctrl+R will fill a selection to the Right, and Ctrl+D will fill a selection Down.
Ctrl+A selects the current region, Ctrl+A a 2nd time selects the whole sheet.
Ctrl+F brings up the Find dialog, and Ctrl+H brings up the Find and Replace dialog.
Ctrl+; inserts the current date, and Ctrl+: inserts the current time.
Ctrl+arrow (up, down, left, right) will move the cursor to the beginning or end of the next “block” of cells. For example, starting with this sheet (notice the active cell is C1):
Ctrl+down will select C4. From there, Ctrl+right will select F4. Ctrl+Down will select F11. Ctrl+Left will select C11. Ctrl+down from C11 will select C1048576!!
Holding the shift key with the above arrow commands will keep whatever the active cell was still active! For example, if C4 is active when you use Shift-Ctrl+Down, you’d see this:
If you have a shape selected, then Ctrl+D will duplicate the shape. Here’s what that looks like after Ctrl+D:
Shift/F2 (the F2 key) will insert a comment!
Ribbon
Okay, now for a totally different set of keyboard shortcuts. You have access to all the commands in the ribbon from the keyboard. If the first key you hit is either a slash or the Alt key, the ribbon will take on letters. For example, here’s part of the ribbon after pressing a slash:
The letters in the small black squares can be used to access that ribbon tab. For example, if you press “A” at this point (indicating the Data tab) you would then see this (truncated):
Not only are you now on the Data tab, but you have a whole new set of letters you can further select.
So, if you started by pressing “/AT”, meaning Data, then Filter, you might see the filter dropdown arrows (depending on where the selection was):
You can learn or memorize your favorite keystrokes. Each of the above examples works for beginning with slash or Alt.
Also, if you have a command that you use a lot, but you have to keep changing tabs to get to that command first, you can put that command in the QAT (Quick Access Toolbar). Those are the little icons above the ribbon. My QAT is already quite large and looks like this:
The little arrow on the far right side contains “common” commands you can add or delete from your QAT:
The checked items are already being used by me. But let’s say you want to have a quick access to creating a pivot table. You can go to the Insert tab, right click on the Pivot table icon, then choose the command to Add to Quick Access Toolbar.
Then, the right of your new QAT would look like this:
Finally, you can visit the “backstage view” for more options. (That’s what you’re looking at when you click on the File menu). You can either get there from the right-click of a ribbon command:
Or by File/Options/Quick Access Toolbar:
Either will take you here:
The right side has the list of items already in your QAT (in this case, my QAT) Notice the dropdown above this in which you can have separate QATs depending on whether you choose “For all documents (default)”, or only for the current workbook!
The left side lists all the popular commands. You can change this dropdown to many choices:
My favorite is to select “All commands” where you’ll be able to find a QAT for any command in Excel!
You can tell from the small scroll indicator that this is a very long list!
Go make your Excel life easier!
If you are interested in bringing Excel MVP Bob Umlas into your company (either virtually or traditionally) to do Excel consulting and/or training work, contact roxi.nevin@iil.com.
About the Author
Bob Umlas has been using Excel since its inception in 1986. He has had more than 300 articles published on subjects ranging from beginner to advanced, VBA, tips, shortcuts, and general techniques using virtually all aspects of Excel. He is the longest running Microsoft Excel MVP in the world (25 years). He has been the technical editor of (about 20) of Bill Jelen’s (MrExcel) Excel books. Bob speaks at Excel conferences around the world on his favorite topic, Tips & Tricks, which wows even the experts. He is the author of 3 Excel books and is the current leader of NYC’s Excel Special Interest Group (which meets on the 2nd Tuesday of each month).
Books:
This isn’t Excel, it’s Magic!
Excel Outside the Box
More Excel Outside the Box
Dates and Times in Excel
By Bob Umlas, Excel MVP
After reading this blog you should be an expert in Excel’s Dates and Times.
If you enter the number 1 in cell I1, for example, then look at the Home tab’s Number Format’s dropdown, you’ll see this (without the arrows!):
Notice that the number 1, formatted as a short date (dates as month/day/year), is January 1, 1900! (Also notice the time is 12:00:00 AM, but I’ll cover that later). Well, if 1 is January 1, what’s 0? Yes, it’s January 0 (not December 31, 1899)!
Excel stores dates as the number of days since January 0, 1900.
This article was written on April 11, 2017. Look:
What’s interesting here is the date expressed as a number: it’s 42836. That’s 42,836 days since January 0, 1900! So, Excel stores dates as numbers, but these numbers are simply formatted to look like dates. This version of a date, formatted as a number, is called a Serial number. The number 200,000, formatted as a date, is July 30, 2447!!
So now Excel can work with dates as simply as it does with numbers. Try finding out how many days old you are: You would enter a formula like =TODAY()-“5/6/42”. What you’d see probably makes no sense, at first: 12/6/1974, in my case! What’s that? It’s a number formatted as a date. The actual number of days is 27369:
You just need to format it as General. You can avoid the need to reformat the cell by using the DATEDIF function shown below. By the way, why did I use quotes around the date (=TODAY()-“5/6/42″)? If I hadn’t, it’d be treated as 5 divided by 6 divided by 42, not the date 5/6/42!! As a validity check, see the difference between entering these two: 5/6/42 or =5/6/42. You might even try entering =1*”5/6/42” – this too will give you the Serial number of 15467.
Excel has many functions to work with dates: MONTH, DAY, YEAR, TODAY, DATEVALUE, NETWORKDAYS, WEEKDAY, WORKDAY, DATEDIF and others. Using 4/11/17 as our date (and say it’s in cell A1), then here’s a sampling of these functions:
If you have =TODAY() in a cell and it returns 4/11/2017, then if you open the same workbook the next day it will say 4/12/2017. If you simply open the workbook and close it, you will be asked if you want to save changes. Why? You didn’t change anything! It’s because the TODAY function (among others) is a volatile function and is “always updating”.
Dates can be formatted in many ways. Here are some common ones, all using 4/11/17:
Suppose you have 4/11/17 in cell A1 and you want to refer to it from another cell, but with some text. You want it to say “Today is 4/11/17”, so you try =”Today is “&H1 but you get a surprise:
You need a new function for formatting that date, since cell H1 actually contains 42836, not 4/11/2017! Here’s the solution:
It’s the TEXT function. Here’s where you supply the formatting as in the table above. One more example (refer to the table above):
Okay, now let’s look at TIME. Time is similar to Date – it’s all formatting. Time is kept as a fraction of a day. Have a look at this initial example:
Notice that the formula in cell G3 is =G1=G2, and it’s returning TRUE. This means 6 PM is equivalent to .75. And similarly, column H shows 12 PM is equivalent to .5. There are 24 hours in a day. 12 PM is half-way through the day, or .5. 6 PM is ¾ through the day, or .75! What about 3:30 PM?
So, time is stored as a fraction of a day. Midnight is 0. This exact moment (it’s now about 3:37PM) is shown here via the NOW function:
It’s all formatting! Cell J1 is formatted as m/d/yyyy h:mm.
Here are some TIME built-in functions:
Here’s an anomaly you may have come across. Look at this spreadsheet:
The value in cell B7, summing the values in B1:B5, clearly seems wrong! In fact, it’s right, but it’s formatted wrong! Since time doesn’t go beyond 23:59:59 (1 second before midnight), once you would hit 24:00:00 (invalid), it’d be reset to 0. So, the value in B7 is right and would be displayed as you see in cell B9. Regular formatting of h:mm forces hours to be 0-23 only. The trick in cell B9 is to use the format of [h]:mm! The [h] overrides the hours to not rewrap at 24. The formatting as minutes (mm) also has an override of [mm], as does seconds [ss].
Here’s the display of cell B7 shown with [mm] and [ss] formats:
You are now officially an Excel Date & Time expert!
If you are interested in bringing Excel MVP Bob Umlas into your company (either virtually or traditionally) to do Excel consulting and/or training work, contact roxi.nevin@iil.com.
About the Author
Bob Umlas has been using Excel since its inception in 1986. He has had more than 300 articles published on subjects ranging from beginner to advanced, VBA, tips, shortcuts, and general techniques using virtually all aspects of Excel. He is the longest running Microsoft Excel MVP in the world (25 years). He has been the technical editor of (about 20) of Bill Jelen’s (MrExcel) Excel books. Bob speaks at Excel conferences around the world on his favorite topic, Tips & Tricks, which wows even the experts. He is the author of 3 Excel books and is the current leader of NYC’s Excel Special Interest Group (which meets on the 2nd Tuesday of each month).
Books:
This isn’t Excel, it’s Magic!
Excel Outside the Box
More Excel Outside the Box
Advanced Filtering in Excel
By Bob Umlas, Excel MVP
In this blog I’ll continue the discussion from my last blog on basic filtering. This one is on advanced filtering.
Let’s continue with the same worksheet used from the last blog:
You can use advanced filtering for any of the features you use basic filtering. The advanced feature enables you to do things that can’t be done with basic filtering. For example, how would you filter this data for items in which the Sales times the Units is over 30,000,000? You can’t, unless you add another column containing this calculation and then filter on that new column. But as you’ll see, you don’t need to do that with the advanced filter. Let’s take a look at what the advanced filter looks like.
It’s on the Data tab:
When you select this, you see a dialog box which you fill in:
If the active cell is inside a range of data, then the first field is pre-filled with the address of the contiguous range of cells. You can change this to be any range, but you usually leave it as is. The worksheet I used for this blog has data in A1:F59. The next, and perhaps most important part of the advanced filter, is the Criteria range. This is a range of cells containing at least 2 rows and at least 1 column. Usually, the top row is one of the column header cells, like Product, or Region in the above illustration. For example:
Cell H5 contains the column header from cell F1, “Region”. The criteria range in the dialog was created by clicking in the field, and then dragging across the 2 cells in column H. (“Filtering” is the name of the sheet tab, and preceded the address if you drag across the cells). You could have simply typed H5:H6 in the Criteria Range box.
But what does this mean? The top row identifies the column, and the 2nd row identifies what to filter this column by. You would definitely not use the advanced filter for this simple filter; I’m just demonstrating how this works. The result, when clicking OK is as expected, rows containing only North Region:
Let’s examine a more involved filter.
This time, the criteria range is 3 rows, not 2. This is how you indicate an or condition. The region is North or West. The result:
When there’s more than 1 column, it becomes an and condition:
This means you want to see all rows which have a Region of North and a Salesperson of Davolio:
You’re not limited to 2 columns. This filter adds 3 fields together:
That means that the Region must be North and the Salesperson must be Davolio and the Product must be Produce. You would enter this information manually before using the Advanced Filter.
The above yields this:
You can also have more than 2 rows and more than one column! This filter will show those rows where the Salesperson is Davolio and the Product is Produce, or the Region is South and the Product is Meat:
Let’s get back to the filter which can show those rows where sales times units is over 30 million. There’s no column you can use, so you resort to what’s called a “computed criteria”. It’s ok to have some kind of labeling in the top row of this column (or it may be left blank) – just don’t use a label that matches something in the headers being filtered, or this will not work as it is intended.
It might look like this:
The top row is clearly blank (H2), so this is a computed criteria. But what’s that “TRUE” in H3? As in all computed criteria, it’s a formula which specifies the criteria, and refers to the first data row (row 2 in this example) as an example to apply to every row. Cell H3 contains the formula =C2*D2>30000000:
It’s the test we want to apply to every row: Sales times Units (columns C and D) must be more than 30,000,000. Here’s the result:
(It’s accurate. I tested it!)
You can combine computed criteria with non-computed criteria as well! In this example, using just B for the salesperson will treat it as a wild card, indicating any Salesperson beginning with B:
Cell H3 contains the formula =YEAR(B2)=2017. This criteria is getting all rows in which the year is 2017 (even though the dates contain month and year), and the Region is North and the Salesperson begins with B:
If I wanted to see some field which is exactly equal to just B, and not just beginning with B, you have to use a very non-intuitive formula: =”=B”.
If you want something to be not equal to something, you could use this as the criteria:
You may also have noticed the choice in the filter dialog to Copy to another location.
This advanced criteria will copy all records in which the region contains an s (all except North) to cell I1 as the starting cell:
Notice that the “North” records are filtered out (See column N).
Here’s a trick that not many people know about. If you have cells already filled in with the data headers you want, as in J1:K1 below, you can limit or reorder the extract columns. This is easier shown than explained. Suppose you have your worksheet set up like this:
Note the computed criteria of C2<3000, meaning the filter will be for Sales under 3000:
The “trick” part is that the copy to location is J1:L1, and those cells already contain information. See what happens:
Only those columns which were referenced are extracted from the source data!
What if I wanted to copy to a different sheet, like Sheet3, below?
This gives an error message:
Hmmm. What if we start from that sheet?
Notice I’m starting the advanced filter from a totally empty sheet! I can fill in the fields:
(The criteria range is pointing to Region of North or West):
These records were “pulled” from the Filtering sheet. Cool! And if the empty sheet had Units in A1 and Product in B1:
That extract which points to these cells:
gives:
Okay, nearing the end. What is Unique records only?
Using this advanced filter:
(Notice the list range was changed to be column F only), and cell H5 is empty. When I click OK:
I have a list of unique items from the Region field.
Happy Advanced filtering!
If you are interested in bringing Excel MVP Bob Umlas into your company (either virtually or traditionally) to do Excel consulting and/or training work, contact roxi.nevin@iil.com.
About the Author
Bob Umlas has been using Excel since its inception in 1986. He has had more than 300 articles published on subjects ranging from beginner to advanced, VBA, tips, shortcuts, and general techniques using virtually all aspects of Excel. He is the longest running Microsoft Excel MVP in the world (25 years). He has been the technical editor of (about 20) of Bill Jelen’s (MrExcel) Excel books. Bob speaks at Excel conferences around the world on his favorite topic, Tips & Tricks, which wows even the experts. He is the author of 3 Excel books and is the current leader of NYC’s Excel Special Interest Group (which meets on the 2nd Tuesday of each month).
Books:
This isn’t Excel, it’s Magic!
Excel Outside the Box
More Excel Outside the Box
Basic Filtering in Excel
By Bob Umlas, Excel MVP
In this blog I’ll discuss filtering. In another continued blog, I’ll talk about advanced filtering. First, what is filtering?
We’ll define it by looking at a very simple illustration. Suppose this is your worksheet:
If you were only interested in the West region, you can show just the West items by filtering. There are many ways to filter and we’ll show them all in this post, but for now, if you right-mouse click on the word West in cell A3 (or A6), you can filter this way:
And that will show:
The rows were filtered to only show what you requested (aside from the header row). Notice that rows 2, 4, 5, and 7 are not shown, and the row numbers have turned blue. They’re not deleted, they’re filtered. Also, at the bottom of the window is a small statistic on the results of what happened:
In this case, it showed 2 records out of the 6 available. Also notice the filter icons in row 1. The one in A1, , is an indicator that this column has a filter applied to it. The one in B1, , indicates this column has no filter applied, but it is available. These symbols came into view when you requested to filter by the cell’s value.
If you were to click on the icon in A1, you’d see this (I excluded the parts which apply to sorting):
Here, you can clear the filter, or you can check any of the checkboxes to show or hide (filter, actually) whichever items you want. We’ll discuss the disabled Filter by Color and the Text Filters and the Search box later.
Let’s take a look at a more involved worksheet:
You can put the filter icons on in several ways. Select one cell in the range and…:
- Ctrl/Shift/L (a toggle)
- Click the Filter icon in the Sort & Filter group of the Data tab (notice the tool tip says Ctrl+Shift+L):
- Press Alt/A/T
- Ctrl/T or Ctrl/L (aside from putting on the filter icons, it turns it into a table – a future blog post).
Once you have the filter icons present, you get different results depending on the data. If I put on filters and click the one in column A (Product), I see:
But if I click on one with dates (Column B):
And each of these can be expanded with the + signs – here, I opened up 2016 and then March:
Notice it shows only the available dates from the actual data in column B.
If I select column C, Sales, I see:
It shows all the various unique numbers, in sequence!
It’s time to look at some other features contained in these dropdowns. Let’s go back to the Product dropdown and look at that item called “Text Filters”:
You can filter by many choices. Suppose I choose “Contains…” and then decide to filter on any produce containing the letter “e” (each choice gives a different next option):
When I click OK:
Notice “Dairy” is not shown because there’s no “e” in Dairy! Clearly, I can’t show all possible combinations, and you’ll have to experiment, but this will give you a good idea of what’s possible.
When I look at column B and expand on Date filters, I get this powerful set of options. Shown also is choosing “All dates in Period…” which has a 2nd flyout:
When I look at column C and expand on Number Filters, I see this:
As you can see, there are many choices. For example, if I choose Greater Than, I get another dialog (any command which ends in “…” means there will be another choice):
Here I chose Sales greater than 7000 and saw that I also had another choice, so I selected the “And” option button, then “is less than”, then I entered 9000.
The result:
One last topic for this filtering blog. You may have noticed the Search field. Using a different set of data, here’s a sample of what I see when I click on the filter dropdown:
If I want to filter this long list to anything to do with maintenance, I could type “Maint” into the search box, and after a short pause (this file had 68000 rows!):
Notice it picked out all descriptions which had Maint (had I typed Maintenance, I would have missed the “Maint & Repair”):
Notice above that there’s a checkbox for “Add current selection to filter”. If I wanted to include all rows which have “rep” in the description without un-doing the current filter, I check this box:
Last item I’m covering in this blog is filtering by color. For this example, I colored several cells’ font, several cell’s fill color, and added conditional formatting, so when I choose Filter by Color, I see this flyout:
All the different combinations are listed. If you were to right-mouse click on a cell, your choices are given by this left- flyout when you choose Filter:
Notice all the filtering is done using the selected cell’s property, whereas in the previous filter by color (a misnomer, actually, because you can also filter here by icon), you can see all the choices available.
Next month, we’ll show what can be done with advanced filtering. For example, in the “more involved” worksheet shown earlier, there’s no way to filter the rows to only show those where the sales times the units is over 30,000,000! Stay tuned!
Happy filtering!
If you are interested in bringing Bob Umlas into your company (either virtually or traditionally) to do Excel consulting and/or training work, contact roxi.nevin@iil.com.
About the Author
Bob Umlas has been using Excel since its inception in 1986. He has had more than 300 articles published on subjects ranging from beginner to advanced, VBA, tips, shortcuts, and general techniques using virtually all aspects of Excel. He is the longest running Microsoft Excel MVP in the world (25 years). He has been the technical editor of (about 20) of Bill Jelen’s (MrExcel) Excel books. Bob speaks at Excel conferences around the world on his favorite topic, Tips & Tricks, which wows even the experts. He is the author of 3 Excel books and is the current leader of NYC’s Excel Special Interest Group (which meets on the 2nd Tuesday of each month).
Books:
This isn’t Excel, it’s Magic!
Excel Outside the Box
More Excel Outside the Box
The Page Layout Tab, Part 3 of 3
By Bob Umlas, Excel MVP
For the final part of the Page Layout tab series we’ll complete the discussion of what’s available on the tab and a few other items. The Header/Footer tab of the page Setup dialog is where we left off. Let’s look at this again. Click the dialog launcher below the Print Titles icon and then click the 3rd tab, and you see:
The main buttons here are the Custom Header and Custom Footer buttons. Either one gives the identical dialog aside from the results being shown in the Header or the Footer. The dialog looks like this:
Before we go into all these buttons, notice the tab, “Header” at the top of the dialog. If, in the previous dialog, the box “Different odd and even pages” was checked, then when the Custom Header button is clicked you’d see these tabs at the top:
and if the “Different first page” was checked, then you’d see these tabs at the top:
and if both were checked, you’d see these tabs:
The icons in the middle of the dialog are these:
If you type “My left header” in the left section, select that text, then click the Format Text icon (pointed to by the arrow), you will get the Font dialog as shown below. This is where you can format the selected text. What’s shown here is the effect of doing exactly that, then issuing the command again where you can see the result of the text’s formatting:
In the next dialog, the cursor was placed in the Center section, the page number icon was selected, the word “of” was typed (surrounded by a space), and then the number of pages icon was selected:
The &[Page] and &[Pages] are “codes” for Excel to insert the current page and total number of pages. For example, the following preview would show if this were done to the center section of the footer on a document with 19 pages and we were looking at page 2:
Some of the other commands are pretty self-explanatory. Here the Insert Picture icon was selected (an Excel logo was chosen), then the Format picture icon was chosen where the image was set to be 1” x 1”:
The result:
If the margins are also set, then this would show as:
Where can you set the margins in the Print preview? Scroll down a bit and you’ll see 2 icons at the bottom right, quite hidden from the user (!):
The left one is the toggle for the margins, which you can drag around, and right one is to zoom in.
The last icons on the Page Layout tab we’re discussing are these:
The # of pages wide or tall can be set, or the scale can be set. For example, if you set both Width and Height to 1 page, then the Scale would automatically be set (and no longer enabled), something like this:
The last 4 checkboxes are used to display and/or print the gridlines or headings (headings being the row numbers and column letters).
Suppose you make all your page settings as you like, and then realize you have other sheets to which you’d like to make the same settings! Do you have to repeat all the steps for each sheet? No! You can ctrl/click or shift/click the other worksheet tabs to put them into a group. Here, Sheet4 is active but Sheet3 is now part of the group: 
.
The top of the screen shows this: 
. The word [group] indicates more than one sheet is included in the group. Now all you need to do is bring up the page setup dialog and simply click OK! The settings for the active sheet are copied to all the other sheets in the group!
If you click on the Page Break Preview icon at the bottom right of the screen,
you would see an editable preview of the printout:
The dotted lines indicate where the page breaks will automatically occur. You can actually click and drag these to a new location. Dragging the vertical break to the right would be inserting a manual page break. Here’s the same data with the vertical break dragged all the way to the right:
The icon to the left of the Page Break preview at the bottom of the Excel screen is the Page Layout tab. This gives the following view:
Notice you can see the header at the top, rulers are present above the columns headings and to the left of the row headings, the page break between columns J & K are shown, and you can still edit the workbook in this view. You can also click where it says “Add Header” (or “Add Footer” if you scroll down) and you’ll see this:
Notice the new ribbon tab “Design” in the contextual tab “Header & Footer Tools” which gives you access to the items as icons we saw previously! Here are the definitions of the right-most checkboxes in the above illustration:
These different views of the spreadsheet are also visible from the ribbon’s View tab in the very left section, as seen here:
Join Bob at IIL’s IPM Day virtual conference on November 3rd, where he’ll be presenting on “The Magic of Macros.” Learn how Excel’s Macros (a.k.a. VBA) can create incredible results in the blink of an eye!
Conference Overview: http://www.iil.com/international-project-management-day/
Speakers: http://www.iil.com/international-project-management-day/speakers.asp
Register with promo code BLOG for 10% off!
Bob Umlas has been voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1994 for his contributions to Excel online forums and he is known world-wide for his expertise. As an MVP, he meets yearly with fellow MVPs at Microsoft’s headquarters in Redmond, where he has access to the product developers. He has also been a beta tester for new versions of Excel since version 1.5 and is the author of several books including This isn’t Excel, it’s Magic!, Excel Outside the Box, and More Excel Outside the Box.
The Page Layout Tab, Part 2 of 3
By Bob Umlas, Excel MVP
Last time with the Page Layout tab, we left off with the “Breaks” icon. Next is the “Background” icon. This puts a graphic in the background of the sheet. This background does not print, it merely shows on the physical viewing of the sheet on your computer screen. When you click it, you get this dialog:
From here, you can find an image from a file which already exists in your computer, or from a Bing image search, or from the cloud or Facebook! Here, a Bing Image search for “candy” yielded this result:
When the top right picture of candy corn is selected, and you click the Insert button, you see this:
Notice that the command “Background” became “Delete Background”. If you use this command, be sure to have your image be a light one because it’s very difficult to see any data! Did you notice the $145.00 in cell H15?
The next command is the Print Titles command. When you click this command, you get this dialog:
Above shows that row 1 will be repeated at the top of each printed page. Notice the dotted line after row 55. This indicates where the page break will occur. This is a “natural” page break, as opposed to one which you set by clicking on the “Breaks” command (which would show as a solid line). Without setting the print titles, page 2 and beyond would not have column headings. Setting this command to row 1 ensures the headings will show at the top of each page.
Print titles can only be entire row(s) and/or entire column(s). For example, if $1:$2 were selected as the “Rows to repeat at the top” then the empty row 2 would also be apart of the rows repeated at the top of each page. In addition, if $A:$A were selected as “Columns to repeat at left” then that would print at the left of every page. The order of the printout can be Down then over or Over than down, as seen in the dialog.
This command shows the same dialog as the Custom Margins dialog (seen in Part 1 of 3), except the “Sheet” tab is the active one instead of the “Margins” tab.
This dialog, also known as the Page Setup dialog, can also be seen by clicking the little “dialog launcher” arrow under the Print Titles:
Here, you can access any of the 4 tabs of the Page Setup, which includes all the commands we’ve been showing in the Page Layout tab, so it’s the most powerful of all these commands because it’s all in one place. We’ve seen the Margin and Sheet tabs already, so here are the other 2 tabs:
- The Page tab, where you can set portrait or landscape, the zoom factor of the printout (shrink so more will print on one page, or expand so less will print), how to fit the printout (for example, if you see that it takes 2 pages to print everything but the 2nd page has 3 rows only, you can click the “Fit to” option to force it to print on one page), the paper size, print quality, and what to use as the first page number (1 being the default). You can also remove the value in the number of pages wide or tall, and then Excel will adjust as necessary. For example, if you set it to be 1 page wide by (blank) pages tall, then it will print as many pages as necessary but will be one page wide.
- The Header/Footer tab, where you can set what shows on the header and/or footer of each page.
Next we will be examining this last tab as well as the remaining items related to printing!
Bob will be presenting on “The Magic of Macros” at IPM Day 2016, going live November 3. Learn more about IPM Day here, and register with promo code BLOG for 10% off.
Bob Umlas has been voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1994 for his contributions to Excel online forums and he is known world-wide for his expertise. As an MVP, he meets yearly with fellow MVPs at Microsoft’s headquarters in Redmond, where he has access to the product developers. He has also been a beta tester for new versions of Excel since version 1.5 and is the author of several books including This isn’t Excel, it’s Magic! (available on the IIL Bookstore), Excel Outside the Box, and More Excel Outside the Box.
The Page Layout Tab, Part 1 of 3
By Bob Umlas, Excel MVP
It seems not much has been written about Excel’s Page Layout tab so I’ll cover the items shown here (which do not include all the commands) up until the Breaks icon. In parts 2 of 3 and 3 of 3, I’ll cover the icons from Background to the end, as well as go over the Page Setup command:
If you click the dropdown arrow below Margins, you see this:
You can select any of the built-in margins or you can modify them using the bottom item, “Custom Margins…” It looks like the first 2 choices have identical settings, but the “Last Custom Setting” changes to reflect the last setting you used. Suppose you select Custom Margins:
If you change some of these settings, say the top, left, bottom and right are all set to .5 (inches), then when you click the dropdown next time, you’ll see this at the top:
That way your “favorite” custom setting is saved.
The “Orientation” setting is fairly straightforward and consists only of Portrait or Landscape:
The next icon is “Size”, and has to do with the paper size you’re printing on. Here’s the top part of the list, containing the most common U.S. paper sizes:
Next is “Print Area”:
Here you can set or clear the print area. If you set it then only what you select will be printed, even if there’s a lot more to print. Clear Print Area doesn’t erase anything(!) – it allows for printing everything on the sheet (which is the default). It’s useful if you have previously set the print area and now want to print everything. If you do set the print area, then a name, “Print_Area” is created (or redefined if it already existed):
In the above example, if you select B2:C6 and use the Set Print Area command, then when you print the sheet, only B2:C6 would print (note the name Print_Area in the upper left corner – an area called the Name Box).
Next comes “Breaks”. When you select this dropdown, you will see:
If you have an entire row selected and use Insert Page Break, then you would see a line indicating where the page break falls. Suppose row 4 was selected and you issued the command:
Notice the light line between rows 3 and 4 – this indicates the page break. You can suppress the display of the page breaks in File/Options/Advanced section. If you scroll about 3/4 the way down, you’ll see this checkbox:
In the Print area which was set above, if you did File/Print, you’d see:
And when you request the next page (hey—how is that done? You scroll down and at the very bottom you’ll see this):
and then you can click the arrow next to the 2 and you’ll see the 2nd page:
If only one cell is selected when you issue the Insert Page Break command, you’ll see crosshairs for the page breaks. Here, cell C4 was selected when the command was issued. 4 pages would print (A1:B3 would be on one page. C1:D3, A4:B7, and C4:D7 would each be on separate pages).
The Remove Page break is enabled only if a page break is selected, like C4 in the previous example.
Reset All Page Breaks removes any page breaks set, and also changes the print setting for the scaling. That is, if you set the print to be 80% (we’ll discuss this in one of the next articles on Page Setup), then that would be reset to 100% after using the Reset All Page Breaks command.
[trx_infobox style=”regular” closeable=”no” icon=”icon-desktop”]Learn more about IIL’s Microsoft Excel training >> [/trx_infobox]
Bob Umlas has been voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1994 for his contributions to Excel online forums and he is known world-wide for his expertise. As an MVP, he meets yearly with fellow MVPs at Microsoft’s headquarters in Redmond, where he has access to the product developers. He has also been a beta tester for new versions of Excel since version 1.5 and is the author of several books including This isn’t Excel, it’s Magic! (available on the IIL Bookstore), Excel Outside the Box, and More Excel Outside the Box.
Excel Grand Totals: You DON'T Need Long Formulas
By Bob Umlas, Excel MVP
Still using long formulas to create grand totals in Excel? There is a better way!
Suppose you have a worksheet which looks something like this:
Clearly, what’s needed is the grand total in cell B38. Notice that there are several subtotals in the list. The formula in cell B9, for example, is =SUM(B4:B8).
How many of you would use the formula =B3+B9+B14+B20+B25+B32+B37 to solve the problem?
I’ve seen formulas like this many many times, and often a lot longer because the list is longer. It does work, producing the correct answer, but it’s tedious to do and certainly subject to errors because you might click the wrong cell.
In this blog I’ll describe a few alternative ways to do this, leaving the best for last!
The first way is probably the easiest, but the formula is perhaps just as long. While the cell B38 is selected, press Alt/= (that is, hold the Alt key down and press the = sign). This creates this formula:
Wow! That’s cool! Excel “finds” all the other Sum formulas and picks out the correct cells! Not likely to produce an error! But there’s no hunting for the correct cells to use. One down side to this technique is that it won’t work on a range which is horizontal, only vertical, as this one is.
Let’s look at some shorter ones:
This doesn’t look much shorter, but it’s pretty much the same size no matter how many cells are involved. So it’s definitely shorter than something like
=B1+B6+B11+B17+B20+B26+B32+B37+B40+B44+B50+B54+B60+B66+B81+B90+B99+B106+B112+B121+B126+B130+B134+B154+B159+B144+B176+B188+B192+B198+B206
!!
And I’ve seen longer! (4 lines worth!)
OK, what is SUMIF? It’s a built-in Excel function which works like this: Wherever the first range (A1:A37) contains the word “Subtotal”, add the values from the 2nd range (the 3rd parameter). The actual syntax is:
That’s pretty cool too! But this could also lead to an error if somewhere in column A the word Subtotal were misspelled, or contained a space at the end (“Subtotal ”)!
OK, ready for the winner? Make sure you’re sitting down first.
How about =SUM(B1:B37)/2:
What? How can that work?
Well, let’s look at the formula in B37, for example:
That’s adding up the values in B33:B36. Those values total 2,131. If I add the values in B33 thru B37 (including the subtotal), I get 4,262 – the 2,131 from B33:B36 and the same 2,131 from B37. So I’m adding the values in twice! So if I divide by 2, I get the right answer! Each of the sections containing the subtotal had the values in there twice – the individual values plus the subtotal! So dividing by 2 solves the problem!
This makes the technique work horizontally as well, using a formula something like =SUM(A2:AK2)/2.
Here’s the same data we were looking at, but turned on its side:
Clearly, we get the same answer!
No more lonnnngggggg formulas to do this task!
[trx_infobox style=”regular” closeable=”no” icon=”icon-desktop”]Learn more about IIL’s Microsoft Excel training >> [/trx_infobox]
Bob Umlas has been voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1994 for his contributions to Excel online forums and he is known world-wide for his expertise. As an MVP, he meets yearly with fellow MVPs at Microsoft’s headquarters in Redmond, where he has access to the product developers. He has also been a beta tester for new versions of Excel since version 1.5 and is the author of several books including This isn’t Excel, it’s Magic! (available on the IIL Bookstore), Excel Outside the Box, and More Excel Outside the Box.