Excel

Want to Print Nonadjacent Ranges in Excel?

Normally, we chose an adjacent range of cells with headers for printing in Excel. However, there are occasions we want to print nonadjacent cell ranges. There are some of us who didn’t realize you could do this in a single print area. Let’s see how this is done.

This is for Excel for Office 365 Windows and Mac versions. Other versions of Excel will be similar.

Dilbert Touching — *Copyright Scott Adams, Inc./Distributed by Universal Uclick for UFS*

First Method of Selecting Cells

One method is holding down the “Cntl” key while selecting the range of cells you want in your Print Area.

Select your first range of cells
Press and hold the “Cntl” key
Select additional ranges of cells
Release the “Cntl” key
Click the Page Layout tab
Click Print Area
Click Set Print Area. See following image for my selection of nonadjacent cells in Print Area:

Excel Print Area 1 — Selecting nonadjacent cells Print Area

Please note that each range you selected will print on a separate page in the same order that you selected regardless of cells location. Also, your current page settings will apply to all your pages. For example, you are unable to mix Landscape and Portrait printing.

Second Method of Selecting Cells

Another method is adding nonadjacent cells to an existing Print Area.

Select a range of cells you want to add to your Print Area
Click the Page Layout tab
Click Print Area
Click Add to Print Area. Refer to below image:

Excel Print Area 2 — Adding a nonadjacent cell range to Print Area

As with the first method, each range will print on a separate page in the order selected and with the same print settings.

Locate the Print Area

In the Name box to the left of the Formula bar, click it’s drop down arrow and select Print_Area. See following image:

Excel Print Area 3 — Using the Name box to show the Print Area

Alternatively, from the View tab, click Page Break Preview in the workbook Views group to see the Print Area.

Click Normal to return to default Excel view.

Two Methods on Printing Your Ranges

You may not want each nonadjacent cell range printed on a separate page. You have two options:

Move the cell ranges adjacent to each other and reset your Print Area
Before printing, select the option to Ignore Print Area. The Active Sheet is now ready for printing. Your Print Area is still saved, you are just ignoring it. Refer to following image:

Excel Print Area 4 — Ignoring the Print Area for printing

I Would Like to Hear from You

Please feel free to leave a comment. I would love hearing from you. Do you have a computer or smart device tech question? I will do my best to answer your inquiry. Please mention the device, app and version that you are using. To help us out, you can send screenshots of your data related to your question.

The author’s Vietnam eBook on the Battle for Tra Bong: Events and Aftermath

How to View Two Excel Worksheets From the Same Workbook Side by Side

There is a way to compare the contents in two sheet tabs in the same workbook. Lets see how this is done. In my example, Sheet1 has eleven rows, Sheet2 has ten rows. See following image:

This is for Excel for Office 365 Windows and Mac versions. Other versions of Excel will be similar.

Opening a Second Window for Comparing Contents

In the View tab of the Window group, click New Window

Excel Side by Side 2 — Setting up for a new window

A second window of the same workbook opens, like a clone. At the end of the workbook name is a number denoting the sheet, either a one or a two. The new window becomes the active sheet.

Setting Up Side by Side Viewing

In the View tab of the Window group, click View Side by Side

A Compare Dialog box may appear. If so, choose the sheet you want to compare with the active sheet.

Click View Side by Side. When you select this view, Synchronous Scrolling should already be turned on. This allows you to scrool both worksheets at the same time. If its not turned on, click the View tab, then click Sychronous Scrolling. See below image:

Excel Side by Side 5 — Setting up for viewing side by side

If the two windows didn’t arrange side by side, from the View tab, click Arrange All>Vertical and check the box for Windows of active workbook. Refer to following image:

Excel Side by Side 6 — Setting up how to view the side by side comparison

Viewing the Two Sheets Side by Side

You can now synchronously scroll both up and down, left and right to compare the contents of the two sheet tabs in the same workbook. See final image below:

Excel Side by Side 7 — Comparing the two sheets in same workbook

When you are finished, to restore your window size, close one of the two windows, then click the Maximize Button in upper right hand corner of the worksheet window.

I Would Like to Hear from You

How to Protect an Excel Workbook, a Sheet and its Structure

Do you want to protect your Microsoft Excel workbook or a sheet in your workbook? If so, this blog post is for you. We are going to password protect a workbook, protect a sheet in a workbook and the Excel file itself.

This is for Excel for Office 365 Windows and Mac versions. Other versions of Excel will be similar.

Protect a Workbook

I recommend you start with a blank Excel workbook, just in case you forget a password. To assign a password to an Excel workbook, do the following:

Open a blank Excel workbook. For demonstration purposes, I am using an Excel workbook that contains one sheet with cell data. You can choose an existing Excel workbook if you chose. See my workbook below :

Select File>Save As

Select More options (underneath Excel Workbook drop down box)

Select the Tools drop down box
Chose General Options

Set the Password(s)

You will see notice two password boxes, one to open the workbook and one to modify the workbook

Enter one or both passwords. Make sure you write down these passwords and store them in a safe place.

You have two other options to set if you chose, Always create backup and Enable Read-only recommended. The first is self explanatory. The second option is an extra layer of protection by enabling the workbook to read-only status upon opening.

Confirm your password(s) by reentering the password(s)
Click OK
Click Save
Close or Exit Excel

Note! A user who doesn’t know the second password can open and view the data by clicking the Read Only option. This user can still modify the data, but they are unable to save their changes to the protected workbook. However, the user can save the workbook under a new name, which circumvents the protection process. Of course, password-cracking software will get you into the workbook.

Protect a Sheet

Open a blank Excel workbook. For demonstration purposes, I am using an Excel workbook that contains one sheet with cell data. You can choose an existing Excel workbook if you chose.
Select the Review tab
Click the Protect icon
Click Protect Sheet icon. Refer to following image:

Enter your password in the Password to unprotect sheet box
Click OK
In the Confirm Password dialog box, type the password again
Click OK
Save your changes
Close or Exit Excel

Tip! To remove a password, click Protect>Unprotect Sheet and enter your password. You can also select Protect Workbook icon to protect a workbook which follows.

Protect Excel Structure

Open a blank Excel workbook. For demonstration purposes, I am using an Excel workbook that contains one sheet with cell data. You can choose an existing Excel workbook if you chose.
Select the Review tab
Click the Protect icon
Click Protect Workbook icon. Refer to the following image:

Select the Windows option if you want to prevent users from moving, resizing, or closing the workbook window or hide/unhide windows

Enter your password
Click OK
In the Confirm Password dialog box, type the password again
Click OK
Save your changes
Close or Exit Excel

Protect the Excel File

To prevent users from accessing the data in your Excel file, we are going to protect the file with a password

Open a blank Excel workbook. For demonstration purposes, I am using an Excel workbook that contains one sheet with cell data. You can choose an existing Excel workbook if you chose.
Select File>Info. Refer to my image below:

Click Protect Workbook icon
Chose Encrypt with Password option

Enter a password in the Encrypt Document dialog box
Click OK
In the Confirm Password dialog box, type the password again
Save your changes
Close or Exit Excel

Be cautious when sharing files or passwords with other users. You still run the risk of passwords falling into the hands of unintended users. Remember to write down your passwords and store them in a safe place.

I Would Like to Hear from You

Please feel free to leave a comment. I would love hearing from you. Do you have a computer tech question? I will do my best to answer your inquiry. Please mention the app and version that you are using. To help me out, you can send screenshots of your data related to your question.

How to Create a Microsoft Excel Drop Down List

Drop down lists are used to select specific data, in our example, a list of Salutations. If the user tries to enter a Salutation that is not in the list, Excel rejects the entry.

I am using a simple drop down list in an Excel sheet. This feature is not well known nor initiative.

Salutations was a BIG DEAL for a religious organization that I worked for as a Consultant. The company had a donor base of 25,000+ names/addresses. (Of course, I was using a database, not Excel, to manage the data.) If the Salutation was incorrect on a donor letter, some donors refused to give monies for the company projects. For example, do you address them as Mr. and Mrs. or Dr. and Mrs. or Mr. and Dr.? Some donors wanted no Salutation. I spent a lot time getting the Salutations correct.

This is for Excel for Office 365 Windows and Mac versions. Other versions of Excel will be similar.

Setting Up the Excel Sheet

Enter the headings in Row 1 (Optional)
Create a list of cells E2:E5. Alternatively, you could put the list in another sheet.
Select the empty cell A2. (You can position the drop down list in most any cell or even multiple cells.)

Excel Drop Down List Setup — Setting up Excel for a drop down list

Using Data Validation

Select the Data tab
Select Data Validation
Choose List from the Allow options drop down list
Click the Source control and drag to highlight cells E2:E5. Alternatively, you could enter the cell reference $E$2:$E$5 or use a sheet and cell reference if your list is in another sheet.
Ensure that the In Cell dropdown option is checked. If you leave this unchecked, Excel forces users to enter only the list values, but it won’t present a drop down list. The users would have to guess the values; not a good idea!

See the following screenshot:

Excel Drop Down List Source — Entering the Allow and Source option

Click OK

You can add the drop down list to multiple cells. Select the range of data input cells instead of a single cell. Also, you can select noncontinuous cells by holding down the Shift key while you click the appropriate cells.

You can copy and paste this drop down cell to any other cells in your sheet.

Below is a screenshot where Mr. was selected as a Salutation for cell A2:

First Method of Selecting Cells

Second Method of Selecting Cells

Locate the Print Area

Two Methods on Printing Your Ranges

I Would Like to Hear from You

Share this:

Like this:

Opening a Second Window for Comparing Contents

Setting Up Side by Side Viewing

Viewing the Two Sheets Side by Side

I Would Like to Hear from You

Share this:

Like this:

Protect a Workbook

Set the Password(s)

Protect a Sheet

Protect Excel Structure

Protect the Excel File

I Would Like to Hear from You

Share this:

Like this:

Setting Up the Excel Sheet

Using Data Validation

I Would Like to Hear from You

Share this:

Like this: