H2EPostDropDowns

Create an Excel Drop Down List Without Blanks

by with No Comment dropdownformsfunctionsintermediatenamesoffsetrangestips & tricks

In a previous post, I covered how to create a form in Excel. Although I didn’t go over drop down lists specifically, they are one element you could incorporate into them. The problem is that your list can change over time, getting bigger or smaller. And that can make it difficult to maintain if your list isn’t dynamic as it will involve you always having to manually change the range of your drop down list. Otherwise, it could be incomplete or contain blanks. Below, I’ll show you how you can manually change your drop down list in excel and create it without blanks while also making it dynamic so that you don’t need to worry about whether it changes over time.

Setting up the drop down list

First, let’s start with the basics — creating the list. To create a drop down list in Excel, you just need a series of options to choose from. My list is going to be made up of the top 30 places to visit. I’m just going to put those names in a list.

After entering in the list of places into Excel, the next thing I will do is select all the values, and create a named range. This is as simple as just entering a value next to the formula bar, where you see the cell location. I will call this range VacationSpots:

Named range in Excel containing popular vacation spots.

There is no need to add headers or anything else. Just select the values, enter in a name for the list, and hit enter. A longer approach would be to go to the Formula tab and select Name Manager:

Name Manager in Excel.

Clicking this will show you all of the named ranges in the workbook:

Name manager showing a list of all the named ranges in an Excel workbook.

It shows you the named range I created. However, I could also create one from this screen and also Edit my existing range. This is where you would go to make the change manually. Clicking on the Edit button would give you this screen:

Editing a named range in Excel.

As you can, here I can manually change the address as needed in case the list changes. However, this is obviously not optimal as it can be a tedious process if your list changes frequently.

Creating the drop down

Now that my list has been created, I can set up the actual drop down. To do this, I’m going to select a cell and under the Data tab, click on Data Validation. Here, there is a place to enter your list of values:

Entering in data validation in Excel.

Under the Allow section, I choose List. And for the Source, I enter the ‘=’ sign followed by my named range, VacationSpots. Now, when I click OK and go to the cell that contains the data validation, this is what I see when I select it:

Cell showing a drop down arrow.

Clicking on the drop down arrow will show me my list of options, in the same order that they appear in my list:

Selecting from a list of drop down options in Excel.

I can select any of the values and my cell will update accordingly. This is great, but what if I decided to add more items to my list, perhaps 10 or 20 more locations I want to visit? Next, I’ll go the different ways you can create drop down lists in Excel without blanks.

Option 1: Create extra spaces in your drop down list at the very end

Technically this step involves blank spaces, which is not what this post is supposed to be about. However, I just wanted to show you how this could work. If your list has dozens of items, then having extra blanks may not be that big of a deal. For example, say I edit my named range so that it goes to 50 rows. If you do that and include empty cells, this is the biggest problem you’ll face:

Empty values in a drop down list.

My list no longer starts from the top, it goes to the first blank cell. This can be an annoying problem because now it looks like all of my options aren’t there (they are, but I have to scroll up every time). This is probably the main reason people want to avoid having blank values in their lists. If the blank values simply came after all of your selections, that might be more tolerable. But because they impact where your drop down list begins from, it can be a nuisance.

The good news is there is a simple way to get around this. For all your empty cells, enter just a single empty character. Select a cell, hit the space bar, get out of the cell, and copy that value down. Now, your empty cells technically aren’t empty because they contain a space. And by doing so, the drop down list now starts from the top again. You will still have blank values, but this time they will show at the bottom of your drop down list:

Drop down list with blanks at the bottom.

If this is acceptable then you can stop here. If you are still intent on getting rid of any possible blank value whatsoever, then head over to the next option.

Option 2: Creating a table to create a nonblank list

This option is the easiest method for getting rid of blank values. What you need to do here is convert your list into a table. Select a cell on your list, click on the Insert tab and then click Table:

Creating a table in Excel.

Leave the option for headers unchecked and then click OK. You should see something like this:

Newly created table in Excel.

By default, Excel will apply its formatting and design but you can change the look of it to make it blend in more with your spreadsheet. You can also re-name the header from Column1. Either way, you can now create a new drop down list from this table. Since the values are in range A2:A31 in my spreadsheet, that is what I will enter for my new Data Validation list:

Data validation list in Excel.

You can either select the range, or enter it in yourself. But if you enter it, you need to include the $ signs otherwise it will not auto-update properly. Now, I’ll go to my list add ‘New City’ to the bottom of the table. When I do that, the table automatically expands which you can notice because I haven’t changed the design and so the colors change:

Table in Excel after adding a new value.

And if I go back to the Data Validation settings, my source has automatically been updated:

Data validation list in Excel updated.

This is a really easy way to make your drop-down list automatically update without the need for any formulas.

If the table you are referencing isn’t on the same sheet as your drop-down list, then you will need to use the INDIRECT function reference it. For instance, if you have created a table called Table1 (which should contain just one column for your list) on a different sheet, you can reference it the following way:

This will allow you to reference the list even if it is on a different sheet.

Option 3: Using a formula to remove the blanks in your drop down list

If for whatever reason creating a table isn’t an option for you, you can still create a dynamic list using a formula. Here, I’ll go back to creating a named range. Except rather than selecting a fixed set of cells, I will rely on a single formula. First, I’ll go back to the Name Manger. I’ll create a new named range. The formula for this can look a bit complex so I will break it down into parts.

First, I’m going to use the OFFSET function. This is because it can allow me to specify a height and width, which is crucial to making this work. My data starts in cell A1, and that’s where my formula will begin:

=OFFSET(A1,0,0

A1 is my starting point and that is the first argument. The next two arguments are whether I want to offset and move to any adjacent rows or columns. Since I don’t, I leave those values as zeros. It is the next argument that is critical, as it relates to the height. Here is where I will use a COUNTA function. I want to count the number of nonblank values in my list. My formula looks as follows:

=COUNTA(A:A)

I will embed this within my previous formula:

=OFFSET(A1,0,0,COUNTA(A:A)

For the width, I will set the last argument to 1, since I don’t want to include any extra columns. Here is my completed formula:

Named range in Excel using the Offset function.

You always want to used $ signs in named ranges so that they don’t move on you. Now that this is set up, I can use this NewRange as my Data Validation source. And just like with a table, whether the list gets bigger or smaller, my named range and the drop down list will automatically update.

However, what if your list contains some formulas that look blank but really aren’t? Formulas are a great example of cells that can look empty even if they aren’t. The COUNTA function will count these values and you could again be back to square one with additional blank values. One way you can get around this is by counting the cells that are blanks, and subtracting that from the total rows. The formula would look as follows:

=ROWS(A:A)-COUNTIF(A:A,””)

Using this, you should correctly arrive at the number of cells that contain text and that aren’t blank as a result of a formula You can then insert that formula in your named range, in place of the COUNTA formula:

Named range in Excel using the countif and offset formula.

As you can see, this method isn’t the easiest and that is why I left it for the end. However, there are multiple different ways you can create a drop down list in excel without blanks. But it’s important because by removing blanks, it will make your form or spreadsheet look more polished by not having blank values in them.

If you liked this post on how to create a drop down list in Excel without blanks, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

lockedcells

How to Lock Cells in Excel

by with No Comment formattingintermediaterangessecurity

One of the most frustrating things that can happen to the spreadsheet you’ve spent hours or days on is when someone overrides a formula or a cell with important information. That why it’s important for people making and designing spreadsheets to be familiar with how to lock cells in Excel. It’s not a difficult thing to do and it can save you a lot of headaches down the road. There are two ways you can go about doing it. I’ll start with the approach that should work even on older versions of Excel:

Step 1: Unlock all the cells

By default, Excel sets that status of every cell to locked. However, until you actually protect a sheet none of that goes into effect. But you’ll actually want to undo that. The first thing you should do is select all the cells (CTRL+A) in your sheet and then go into Format Cells (CTRL+1). From there, go into the Protection tab where you’ll see this:

The protect cells tab on the format cells option.

Untick the ‘Locked’ box and this will leave all the cells unlocked. It sounds like the opposite of what you want to do. However, it’s easier to unlock everything and then re-lock the cells you really need locked. Doing the reverse is going to be more time-consuming.

Step 2: Select the cells you want to protect and then lock them

Now, its time to select the cells that you want to lock. Formulas, any sort of rate schedules or information that shouldn’t be changed are things you’ll want to identify. Once you’ve selected them all, you can go back into Format Cells and this time you can re-check the box to have them locked. If any of these cells contain formulas that you don’t want people to see, you can also tick off the Hidden box. Doing this will prevent anything from showing up in the formula bar when someone looks at one of these cells.

Step 3: Lock your worksheet

Even though you’ve already selected which cells you want to be protected, nothing’s technically happened just yet. What you still have to do is lock the worksheet itself. On the Review tab, you’ll have a button to Protect Sheet. Click on that, and you’ll have the following options to choose from:

Protecting a worksheet on Excel.

First thing’s first, you’ll want to put a password in. Otherwise, there’s really no point in protecting these cells if someone can just unlock the sheet without any password.

By default, Excel selects the first to options when protecting cells, allowing users to select both locked and unlocked cells. However, you can untick the locked cells if you don’t want them to be able to even select locked cells. If your goal is just to protect cells, these two options should suffice. But you can also restrict formatting cells, inserting and deleting rows as well.

Once you click OK, if you’ve entered a password, you’ll be prompted to re-enter it again to confirm it. Now your cells are protected. If you attempt to change the value on any of them you’ll get the following error message:

Error message in Excel when trying to change the value of a locked cell.

If you want to be able to change those cells, you’ll now have to go back to the Review tab and click on Unprotect Sheet. There, you’ll be asked for a password if you’ve entered one.

Optional step: highlight the cells you plan to lock

To prevent frustrating your users, you may want to take an additional step and highlight the cells that they either should or shouldn’t enter values in. This will avoid them getting frustrated with error messages popping up when they try and make changes. Highlight cells can make it a lot easier for users to identify which cells they should edit.

Tip: here’s a quick way to to find all your formulas

If you don’t want to hunt down all the formulas in your sheet that you want to protect, the good news is it’s easy to find them. You can use CTRL+~ to toggle showing formulas or values on your worksheet. However, there’s an even easier way to select all your formulas. By pressing F5 you’ll get the Go To box:

Go To options in Excel.

Click on Special (this is greyed out once you’ve locked the worksheet, so you’ll want to do this beforehand). Then, click on Formulas in the next menu:

Selecting formulas in the Go To Special box.

Click OK and now all your formulas will be selected. From here, you can go to the Format Cells options and make sure they’re locked or hidden.

What if you’ve made a mistake and protected the wrong cells?

If you need to make a correction to which cells you’ve selected, all you need to do is to unprotect the sheet. Then repeat steps 1-3. But you may notice something odd when you go back to unlock all the cells:

Protected cells showing a mix of unlocked and locked cells as well as hidden and unhidden.

This is how the checkboxes will show up if not all cells are locked and not all cells are hidden. You’ll only see the boxes blank or ticked off if all the cells have the same values (e.g. protected or hidden). Clicking once on each of these text boxes will turn them into checkmarks:

All selected cells are hidden and locked.

If you were to click OK then all the cells would be locked and hidden. Clicking these checkboxes an additional time will make them all unlocked and unhidden (but not until you protect the worksheet):

All the cells are set to be unhidden and unlocked.

The alternative approach: Protect cells in Excel using the Allow Edit Ranges option

Below, I’ll show you another way how to lock cells in Excel that doesn’t require unlocking and re-locking cells. Rather than following steps 1-3 what you can first do is use the Allow Edit Ranges button on the Review tab. Pressing that button will give you the following options:

Allowe Users to Edit Ranges options.

From here, you can click on the New button. Next, select a range that you want users to enter data on:

Creating a new range that users can edit.

In this example, I’ve set a name of Range1 to everything in column A. I’ve also put no password for this range. Now, if I go to protect my entire worksheet, I can still edit any cell in column A (regardless of it is set to locked or not). If I had added a password for that range, I’d be prompted to enter it before gaining access to the range:

Entering a password to unlock a range.

You also have the option to designate certain users who don’t need a password to edit the range. You can do this by clicking on the Permissions button:

Changing the permissions of who doesn't need a password to access a specific range.

What’s also convenient about the Allow Edit Ranges feature is that you also can protect the sheet right from its options.

Which method should you use?

Ultimately, both methods can help you protect your cells and formulas in Excel. If your worksheet is very structured and there are only certain places where users should make changes, then using the Allow Edit Ranges approach probably makes a lot more sense.

If, however, most of your cells should be editable and there are only a few cells that you need to lock or they’re spread out all over the place, then the first method may be preferable.

Either approach can work and it may just come down to your personal preference on how to lock cells in Excel.

Adding additional controls—locking down the workbook

If you want to take things a bit further and prevent people from modifying, viewing, or deleting worksheets, what you can do is protect your workbook as well. This is a simpler than how to lock cells in Excel and all that you need do here is just click on the Protect Workbook button and put a password on your entire workbook. Doing this will prevent users from renaming, moving, deleting, or hiding worksheets.

If you’d just prefer users don’t see certain worksheets and don’t even see that they’re there, you may want to consider using VBA to hide them without having to lock your entire workbook down. In some cases, locking a workbook may work well at preventing worksheet changes, but it’s an all-or-nothing approach: you can’t pick and choose which worksheets they can change — that’s where VBA can help.

If you liked this post on how to how to lock cells in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Pivot Tables: Avoid Changing Data Sources with Named Ranges

by with No Comment advancedpivot tablerangestips & tricks

When working with a pivot table, you determine a data source to use for that table. But what if later you add rows or columns? You’ll normally have to change the data source to include the updated range, otherwise your pivot table isn’t including your changes. Unless you use a named range.

First, select the Name Manager under the Formulas tab and click on New

However, instead of selecting all the data, I’ll use the offset formula.

The OFFSET function allows you to specify the size of your data set. The formula below will keep the range equal to all the nonblank rows and cells starting from cell A1:

=OFFSET(Sheet1!$A$1,0,0,counta(Sheet1!$A:$A),counta(Sheet1!$1:$1))
This is the first cell of your data source

This is the first column of your data source

This is the first row of your data source

Once you’ve created a new name for your data set, put the formula above in the Refers to field and press OK:



Now you’ve created your custom, auto-updating range. All you need to do now is when creating a pivot table (or changing its data source), put in the named range.

Note this will not work properly if you have gaps in your columns or rows. The COUNTA function counts how many non-blank cells are within the range. So you could manually override the formulas if need be, but that would defeat the point of this post.

But if you follow the above steps and use the formula above, your pivot table will automatically be updated with any new rows or columns you add to it. All you’ll have to do is refresh the pivot table. And because the offset formula forces the range to change, that will automatically happen in the pivot table as well.

How to Create a Named Range in Excel

by with One Comment intermediateranges
Naming ranges offers an easy way to refer to a single cell or range without having to remember the address, and instead referring to it by name instead. This comes particularly useful if you will continually be referring to that cell. 
The key advantages of using named ranges:
– Less time consuming to find a named cell
– Easier to put into formulas
– Easier to reference in VBA
– Easily inserting hyperlinks in documents

Creating Named Ranges


Below I have sales data listed by month. If I wanted to reference total sales I would need to refer to cell B14.
Let’s say this sheet is just one of many, and these are sales for product A.  The more products I have, the more useful it becomes to have named ranges, otherwise I would have to either a) remember the location when typing a formula, or b) navigating to the cell I want to reference. By using a name, it is easier to recall and doesn’t take much effort to include in a complex formula that may include many references.What I could do is name cell B14 as ProductASales (and subsequent ones could follow the same patter – ProductBSales, ProductCSales, etc..)
All I do is select cell B14 and type ProductASales in the cell reference that previously had B14. B14 still exists, but now I can reference the cell by just typing in ProductASales as well.
A range can have more than one name. If you want to delete or edit a name select the Name Managerunder the Formulas tab
I find that I have created two names for the same range. To delete one I just select the name I don’t want and click Delete. Now if I just wanted to change the range I can click Edit

There I can change the range to whatever I want.
Named ranges can also be used for multiple cells in the same way.

Note that a named range will move as a relative reference. If you insert or delete rows the name will move along with your cells. It can also expand, just like any other range in Excel. If for instance you insert a row between May and June, that will add that row to the range. Instead of including the cells from A2:B13, it will now include the cells from A2:B14 – since December will be pushed down to row 14.

Now that the ranges are setup, they can be referred to in a formula.

In cell H8 you can see that I entered a formula consisting only of the named range ProductASales. Instead of referencing cell B14 I can use this name.
This won’t work the same way for the Sales range since it relates to more than one cell. However, I can use it as part of a formula, vlookup for example:
Here I am using Sales as a table. Instead of typing out the full range A2:B13 I only have to type in Sales.

Apply Named Ranges to Existing Formulas


But let’s say you didn’t realize you could use named ranges and have been manually entering the ranges. You can update your formulas so they reflect the named ranges by doing the following:
Under the Formulastab, select Define Name, and Apply Names.
 At the following screen I select both names and press OK.
Now all my formulas with B14 or A2:B13 will be replaced with ProductASales, and Sales, respectively. This won’t affect your formulas in any way but now if you look at a formula you can easily see if it is referencing a named range.

Using Named Ranges in VBA


Another benefit of using named ranges is that it makes it easier to keep formulas consistent. In VBA, to reference these ranges, unnamed, would be as follows:
Worksheets(“Sheet1”).Range(“B14”)
Worksheets(“Sheet1”).Range(“A2:B13”)
The problem with this is that if you move the cells around, cut, copy, the references in VBA are absolute and will never update the way relative references in Excel will.  However, if you use named ranges, this problem is avoided. The reference would be as follows:
Range(“ProductASales”)
Range(“Sales”)
If you are coding in another worksheet, you will have to qualify which spreadsheet the named range is in:
Worksheets(“Sheet1”).Range(“ProductASales”)
Worksheets(“Sheet1”).Range(“Sales”)
Now if these ranges are moved within Excel, you don’t have to worry about updating them within VBA.

Named Ranges as Hyperlinks


By using a named range you can also easily add hyperlinks into your spreadsheet. For example, let’s say on Sheet 2 I want to link to the sales data:
On the Insert tab, select Hyperlink.
Select Place in this Document and you will see a list of the defined names. If I press Sales and click OK this will create a hyperlink in the active cell:
Now if I click on the Sales link in cell D6 it will bring me to the named range that belongs to Sales.