microsoft 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

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

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 3^rd 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.

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:

1. 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 2^nd 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.
2. 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.

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 2^nd 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.

By Bob Umlas, Excel MVP

Here we’ll be exploring the various ways to hide and unhide rows and columns in Excel. The bulk of the discussion will address columns, but it applies to rows as well (unless otherwise noted).

How to Hide Columns

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:

You can type in any number from 0 to 255, however 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.

How to Unhide Columns

Okay, how about unhide a cell? 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:

The 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 an unhidden cell– it’s resetting the column width to a number. When you unhide, it 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 so it looks something like this:

then drag to the right which will reveal column D!

Lastly, suppose you have text like this:

And you want to widen column C so it all fits. There are a few ways to do this. You can drag the column separator between C and D to the right and visually adjust it. 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!

[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.