Category Archives: Intermediate

Intermediate level information

How to Remove Duplicates in Excel: An Easy Guide

In this article, I will show you how to remove duplicates in Excel. While having duplicate data can be useful sometimes, it can also make it more difficult to understand your data. I’ll use conditional formatting to find and highlight duplicate portions of data within Microsoft Excel. Review your duplicate content and decide if you want to remove them.

Remember that when you delete duplicate values, the duplicate data is permanently deleted from your records. Before you go forward with deleting the duplicate content, I highly recommend that you copy the original data over to another worksheet. This ensures that you will not accidentally lose any of your vital information and hard work.

I use an iPhone app to record my food intake and exercise output every day, and also record my weight, albeit infrequently. All of this data is synced to a database in the cloud. The database automatically enters my weight each day, even though I don’t, by using the last known data point. All of this data can be downloaded in a CSV file, opened with Excel, and saved as a workbook file.

Charting my weight from this data is a simple matter, but Excel doesn’t need all of the extra data points. Consequently, I have reason to use the Remove Duplicates feature that was introduced in Excel 2007.

Using Dates with the Remove Duplicates Feature

I have two columns of data with Date in column A and Weight in column B. I want to remove all duplicate Weights but have to be careful because it makes a difference how the dates are sorted.

The file downloaded from the database was sorted in descending order by Date. When using Remove Duplicates I got a different result when the Dates were sorted in ascending order. In each case, the same number of unique Weight values were found but associated with different Dates.

How to Delete Duplicates in Excel

Understand how to delete duplicates in Excel: Apparently, the Remove Duplicates works from the top down so sorting dates in ascending order make sense. Keep that in mind when Date values are part of your data set.

Here is an example of the raw data, on the left, and the results from using Remove Duplicates when the data was sorted in descending versus ascending order.

Steps to Removing Duplicate Data in Excel

Select the data range or make sure the active cell is inside the data range you want to manipulate. Excel is smart enough to pick out the region of data and figure out if there are column headers.

First thing, make sure the data is sorted. I selected cell B2 and sorted the range in ascending order so the first unique Weight value would correspond to the First Date, and not the last.

Select Data tab » Remove Duplicates, which will bring up a dialog box.

Select the column(s) that have duplicate data
Check an see if the My data has headers box is checked (assuming you have column headers)
Click OK and you will eliminate duplicates in Excel

A popup box will confirm the number of duplicate and unique values

If you’re not satisfied with the result, use the keyboard shortcut Ctrl+Z to undo the Remove Duplicates action.

How to Create a Drop Down List in Excel with Data Validation

Those wondering how to create a drop down list in Excel will be relieved to know that it is easier than it sounds. As you may already know, drop down lists make data entry a breeze. For example, if you have ever used such a menu for surveys, polls, and web forms, you know how convenient they are. As tech nerds, we’re happy that such an option exists within an Excel spreadsheet.

Adding a drop-down list to a cell or range using Data Validation is a simple matter. Data Validation is used to define restrictions on what data can or should be entered into a cell. Here we’ll use a List to restrict what values can be entered into a cell. This article walks you through a step-by-step guide of how to create a drop down list in Excel.

How to Create a Drop Down List in Excel for a Cell or Range

Select the cell or range you want to use for a drop-down list, then

Choose Data Validation from the Data Tools group on the Data tab
Select the Settings tab
In the Allow box, select List
Click the Source box
Type in a list of values separated by a comma
Make sure the In-cell dropdown box is checked
Click OK

The list I created was for cell A1, which is shown below.

Excel 2010 Drop Down List

List Data Sources

Manually entering the source data for the Excel 2010 drop down list is probably the least desirable method. A better way is to put the list in a range, then refer to the range.

The same list data was put into the range J1: J6, then I changed the source reference to these cells. This is a better method than manually entering the values, but older versions of Excel require the list to be on the same worksheet. One way around this and a better solution is to give the List range a Name.

You can give the List range a Name then use it for the Source. For example, I selected the range J1: J6 then typed TheNames into the Name box, thereby creating a Named Range. On the Data Validation dialog box, I typed in =TheNames into the Source box.

Change the Reference to the Named Range

Now let’s assume that we have to add a couple more names to the list. Instead of changing every cell that references this Data Validation list, we just change the reference to the Named Range. (Choose Formulas > Name Manager, select the Named Range, change the reference in the Refers to box, then click the green arrow to make the change and click Close.)

But if the list will grow over time, changing the reference should be done automatically with a dynamic Named Range formula. We’ll do this by using the OFFSET formula.

Choose Formulas > Name Manager
Select New
Type a Name in the Name box (I’ll use myNamesList)
In the Refers to box type =OFFSET(Sheet1!$J$1,0,0,COUNTA(Sheet1!$J:$J),1)
Click OK

Now select the cell or range with Data Validation and,

Choose Data > Data Validation
Select the Settings tab
In the Source box type in =myNamesList (or the Name you created)
Click OK

This Named Range formula is dynamic, which means the source list will expand when names are added to the list. If the list contains more than 8 values the drop-down list will have a scroll bar.

Excel – Pick from Drop Down List

A Bonus Tip

One of the things we love the most about creating a drop down list in Excel is that the program reminds you to save your work before you click out. However, the program will not remind you to create a backup. If you do not have an automated backup system in place, we highly recommend implementing one. For example, you can download and save a copy locally to your desktop and USB drive. If you are using a thumb drive, store the saved copy offsite. Does this sound like overkill? Maybe. But you will not think so if you lose your first copy and then find yourself in need of it.

How to Create a Drop Down List in Excel: Final Review

That’s it. We hope you enjoyed reading our article on how to create a drop down list in Excel with data validation. It’s not as hard as you might have thought. Hopefully, you can now create a drop-down list that will meet your needs. Happy organizing!

How to Alphabetize in Excel: Fast & Simple

What is Excel?

If you are a complete newcomer, you can still walk away from reading this article and learn how to navigate Excel. Let’s start with the basics. Before you learn how to alphabetize in Excel, we need to address the question ‘what is Excel?’ The answer is simple.

Microsoft Excel is a software application included within Microsoft Office Suite. You will use this tool to create spreadsheets (aka documents laid out in rows and columns). Due to the program’s versatility, it has become one of the world’s leading platforms in business since its initial launch in 1985.

What is an Excel Spreadsheet?

The next question you should be asking is ‘what is a spreadsheet?’ In short, spreadsheets organize data into easy ways to read and manipulate (rows and columns). An Excel spreadsheet is composed of columns (vertical boxes labeled ‘A, B, C, D…’ at the top of the screen) and rows (horizontal boxes labeled ‘1, 2, 3, …)’) at the left side of the screen. The intersection of each column and row holds cells where a user can enter either numbers or text. When one refers to the address of a cell, they mean the letter of the cell’s column combined with the row (e.g. A4).

How To Alphabetize In Excel

The main reason people use Excel is to organize data into an easy to read list. You want to be able to refer back to information from years ago, and quickly find something you need.

Excel is so useful because it gives you the ability to do just that. You can organize information alphabetically from A-Z or Z-A. This method allows you to create highly functioning spreadsheets that will keep all your data in order.

We’re going to break down:

How to alphabetize in Excel
Organizing columns that contain multiple strings of information
How to sort spreadsheets that have a combination of letters and numbers

Let’s dive right in!

The Reasons for Alphabetizing

For demonstration purposes, let’s say you are the owner of a sporting goods wholesaler and this is your spreadsheet for the month. If you look at the picture below, there is no structure to the data.

If you had a list built this way for ten years and you needed to look back during an audit. Then it would be incredibly difficult and time-consuming to find the information you need.

If we alphabetize the data, we could:

Scan through to find the name of a customer or product
Choose a specific department to document revenue
Find sales information on a particular product
Keep data organized for accounting and tax purposes

Additionally, if you have employees, having organized spreadsheets is even more critical. If you can’t make sense of your data, your employees will never understand it.

Basic Alphabetization

There are a variety of ways to organize information in Excel. However, we will start with the simplest method and work our way up.

Firstly, highlight the columns or rows you would like to sort by clicking and dragging your mouse across the cells.

Click the Sort & Filter button on the top right of your dashboard, and you will be able to sort Ascending from A-Z, or descending from Z-A.

Accordingly, doing this will organize the columns you have selected alphabetically. As you see above, I arranged the Customer column from A-Z.

One thing to remember:

It’s important to make sure you do not have empty spaces or special characters in front of your data.

Advanced Alphabetization

Alphabetizing in Excel is easy when you are sorting one column. Things get interesting when you have multiple columns, and you need to keep them in order as well.

Let’s say you want to organize alphabetically by the customer. But you also want to keep those customers in groups based on departments they purchase from. You can do that using Excel’s custom sort feature.

Firstly, select all your data by dragging your mouse over everything or clicking the arrow pointing diagonally towards your spreadsheet. Then make sure you highlight the entire spreadsheet – if you are missing cells you won’t have everything sorted.

Next, go back into the Sort & Filter drop-down box and select Custom Sort. Your screen may vary slightly depending on which version of Excel you are using.

In the box above, you will see that there are a few options for sorting your data. Under column, you can choose what header you are sorting. For this example, we will sort by “Product.”

The next option you see is “Sort On” – this is what your cell is based on. For example, we will always leave it as “values.” You can sort based on cell color, font color, and more.

Lastly, you can choose the order for which you are sorting. You can sort ascending from A-Z or descending from Z-A when learning how to alphabetize in Excel.

If we do this, we will have our product list sorted from A to Z similar to the first demonstration, but this time we are going to get more advanced and add another level to the sorting.

In the custom sort dialogue box, click add level, and you will see another row appear.

The second line is labeled “Then by” – this level of sorting will follow whatever is in the first line. Whatever column you include here will sort after your first line of data.

For our example, we will use Revenue as our column to sort after we have alphabetized our products.

We will leave “values” as what we are sorting based on and this time we are dealing with numbers instead of letters – we were given a different option for “order.” We want to sort our revenue from Largest to Smallest to find out what products are our best sellers.

Once you have everything set up and you’re ready to organize your data, press OK, and you now have a spreadsheet that is organized by product first and revenue second.

Looking at this organized spreadsheet, you can quickly identify what your best-selling product in the football department is footballs in packs of 5.

Alphabetizing in a Custom Order

Up until this point, we have been organizing data ascending or descending.

You are probably asking – How do I arrange chronologically by month?

If you organize months alphabetically, February will come first so that would not work. We are going to show you how to get even more technical and organize chronologically.

First, go back to Sort & Filter and open up Custom Sort.

We have added a column for the month now.

Under “order” in the custom sort box, you will choose “custom list,” and it will bring up this dialogue box:

Here you will manually enter the order you want the entries to appear. In this example, we want to order the data – January, February, and March. Click OK to confirm then hit OK again to sort.

Finally, you have a completed list that is organized by Product, Revenue, and Month.

Why Isn’t My Spreadsheet Sorting?

It could be the wrong selection. On the off chance that you chose the off-base lines and sections or not exactly the full cell extend that contains the data you need to sort, Microsoft Excel can’t organize your information the manner in which you need to see it. With a fractional scope of cells chose, just the determination sorts. With void cells chose, nothing occurs. To sort every one of your information without making a choice first, click in one cell inside your information extend. When you open the Sort discourse box, you can see a choice zone encase the information that Excel will arrange.

Effectively Sorted

Issuing a sort direction on an informational collection that you’ve effectively arranged, or that you entered utilizing prearranged information, creates no unmistakable outcomes. Excel reacted to the direction you mentioned, but since you effectively composed your data, you can’t sort similar information twice into a similar request. To re-try your sort and really make a redesigned outcome, you’ll have to adjust your sort parameters.

Off-base or Mixed Data Type

On the off chance that you key in a segment of dates into cells that you’ve set up in a blend of content and date designs, your information won’t sort effectively. By blending your cell groups, you’ve set up a line or segment that contains what could be compared to apples and oranges rather than only either. How you show your information additionally can impact how you translate the aftereffects of a sorting task. Dates that show with just their month and day indicating may sort in a startling request since they really originate from various years. When you see odd outcomes, for example, these, you may need to check your information and cell types.

Additional Considerations

Record or application debasement dependably can create unexpected outcomes even in routine programming activities, for example, a worksheet information sort. On the off chance that checking your information and your worksheet configuration doesn’t resolve your arranging issues, shut down Excel and restart your PC to check whether your outcomes change. You additionally can duplicate your worksheet substance, glue them into another Excel record and attempt your sort task there, viably precluding – or distinguishing – your document as the wellspring of your concern.

What is the Difference Between a Workbook, Worksheet, and a Spreadsheet on Excel?

When you open Microsoft Excel (a spreadsheet program), you’re opening an exercise manual. An exercise manual can contain at least one distinct worksheets that can be gotten to through the tabs at the base of the worksheet your at present survey. Often most confounding that a worksheet is synonymous with a spreadsheet. At the end of the day, a spreadsheet and worksheet mean something very similar. Be that as it may, a great many people just allude to the program like a spreadsheet program and the records it makes as spreadsheet documents.

Spreadsheet Programs

Today, Microsoft Excel is the most prevalent and generally utilized spreadsheet program, however, there are additionally numerous choices. Despite the fact that spreadsheets are regularly utilized with anything containing numbers, the employment of a spreadsheet is practically interminable. The following are some other prominent employments of spreadsheets.

Account

Spreadsheets are perfect for money related information, for example, your financial records data, spending plans, charges, exchanges, charging, solicitations, receipts, conjectures, and any installment framework.

Structures

Structure layouts can be made to deal with stock, assessments, execution audits, tests, time sheets, persistent data, and reviews.

School and Grades

Instructors can utilize spreadsheets to follow understudies, figure reviews, and recognize pertinent information, for example, high and low scores, missing tests, and understudies who are battling.

Records

Dealing with a rundown in a spreadsheet is an extraordinary case of information that does not contain numbers, yet at the same time can be utilized in a spreadsheet. Incredible instances of spreadsheet records incorporate phone, to-do, and basic food item records.

Sports

Spreadsheets can monitor your preferred player details or details in the general group. With the gathered information, you can likewise discover midpoints, high scores, and factual information. Spreadsheets can even be utilized to make competition sections.

What is a Functioning Worksheet?

A functioning worksheet is a worksheet that is as of now open. For instance, in the Excel picture over, the sheet tabs at the base of the window show “Sheet1,” “Sheet2,” and “Sheet3,” with Sheet1 being the dynamic worksheet. The dynamic tab ordinarily has a white foundation behind the tab name.

Why Not Use a Word Processor Instead of a Spreadsheet?

While the facts may prove that a portion of the things referenced above should be possible in a word processor, spreadsheets have a tremendous preferred position over word processors with regards to numbers. It is difficult to compute various numbers in a word processor and have the estimation of the computation quickly show up. Spreadsheets are likewise significantly more unique with the information and can hide, show, and sort data to make handling loads of data simpler.

How to Alphabetize in Excel: Final Review

Finally, Excel is an incredibly useful tool for business owners, entrepreneurs, and those who are financially savvy. This covers everything you will need to know about how to alphabetize in Excel. Follow this article step by step, and you will have no problem finding anything and everything you need to know for years to come! If you want to learn how to make a line graph, hiding columns or freezing rows in excel, then we have them for you too.

Pivot Tables (PV Table): Everything You Need to Know

What are Pivot Tables (AKA Pv Table)?

Pivot Tables, also known as Pv Tables, are an Excel tool that allows you to organize data in a way that’s easy to understand. You can use data from a spreadsheet or import a database table to access the information you need. Excel is able to connect to external sources such as SQL Server tables, Azure Marketplace, Office Data Connection (.odc) files, XML files, Access databases, and text files. Making a Pivot Table won’t alter your original data in any way, but instead will arrange it into a tabular format that makes sense and is easier to read than the original spreadsheet.

The Advantages of Using Pivot Tables

The main function of Pivot Tables is to help you organize large quantities of data in a way that’s quick to analyze. You can filter and sort groups into a table that’s more user-friendly than a raw data set or spreadsheet. Pv Tables also make it easy to expand or collapse rows and columns to narrow down your results, giving you a more detailed picture of important data while cutting out unnecessary background noise.

Not only do Pivot Tables make it easier for you to track data more effectively, but they also make it easier to present information. Whether you’re speaking to family members, co-workers, or supervisors, Pivot Tables give a clear and concise picture of your data that’s easy on the eyes.

If you’re not happy with the layout of your Pivot Table, Excel makes it easy to manipulate and reformat information. Not only can you sort, filter, and group data. But you can also add, rearrange, remove, or change the order of fields. You can also easily change the Pivot Table form, choosing between Compact, Outline, or Tabular.

Tables are in compact form by default, but this may not suit your needs if you want headings for Row fields. If this is the case, you can switch your Pivot Table to Tabular Form, which displays one column per field and provides space for field headers, or Outline Form, which displays subtotals at the top of every group.

Creating Recommended PV Table

Pivot Tables are easy to make with just a few clicks of a button. If you’re new to Excel or Pv Tables in general, you may want to start out using Recommended Pivot Tables. This feature automatically comes up with a layout to match your data set. If you aren’t pleased with the final result, you can always experiment by tweaking rows and columns. To create a Recommended Pivot Table:

Click on any cell in your original data set or table range.
Go to Insert > Tables > Recommended Pivot Table.
Excel analyzes your data and presents you with several options based on the categories it detects in your data.
Select whichever table looks like it will best suit your needs. And then hit OK to create a Pivot Table on a new Excel sheet.

The Recommended Pivot Table feature is a relatively new one, introduced in 2013. It’s only available for users that have the Office 2013 suite or above. If you have an earlier version of the software, you’ll have to create Pv Tables manually.

Manually Creating Pivot Tables

Creating a Pivot Table manually is just slightly more complex than making a Recommended Pivot Table. In addition, it gives you more control over your end results and only takes a few more steps. Here’s how you can manually create a Pivot Table to display your data:

Click on any cell in your original data set or table range.
Go to Insert > Tables > Pivot Table.
A box will pop up displaying the Create Pivot Table dialog. You can select and name a range of Excel cells, or import from an external data source. If you want to analyze multiple tables at once, check the “Add this data to the Data Model” box at the bottom of the popup screen.
On this screen, you can also choose whether you want your report to be opened in a new sheet or an existing worksheet. If you choose to place your table in a current worksheet, you need to select both the file and the cell where you want your Pivot Table to be stored.
Click OK, and you’ll see Excel create a blank Pivot Table and display the Pivot Table Fields list. Here, you’ll select the checkbox for any field you want to add to your Pivot Table.

Using Pivot Tables

Once you’ve finalized the formatting of your PV Table, you can take things one step further. You can turn it into a Pivot Chart.

This gives you an even more powerful way to display data. Especially if you’re planning on using it in a presentation. Pivot Charts add visualizations to data in the form of a graph or other chart type.

This makes it easy to summarize data and spot trends and patterns over time. Pivot Charts automatically update when you adjust your Pivot Table.

Conclusion

Excel is a powerful tool both at home and in the office, but datasets and spreadsheets can get confusing. Pivot Tables help you to make the most of your data. It allows you to eliminate unnecessary information and highlight what’s important to you or your business.

By using Pivot Tables, you can quickly come to conclusions and make informed decisions based on large caches of data. Pivot Tables also make it easy to wow bosses, coworkers, and investors during presentations.

Follow the steps laid out in this article. Spare yourself from a headache by effortlessly organizing and analyzing large data sets using Pivot Tables.

Whether you’re keeping on top of your home life or climbing the corporate ladder. Pivot Tables can come in handy when analyzing and presenting data.

Extract Time from a Date-Time Number in Excel

I have a worksheet that tracks start and stop times for different events throughout the day, all during the week. Sometimes I have to pull out the Time of Day, irrespective of the Date, with the TIME function.

The TIME function has three arguments: Hour, Minute, Second. I could use =TIME(11,30,0) in a cell to get 11:30 AM, but I want to convert a Date-Time number so let’s look at an example.

In cell C2 I have the Date-Time value 10/8/12 6:28:30 PM.

In cell B2 I enter the formula =TIME(HOUR(C2),MINUTE(C2), SECOND(C2)) to pull out the time value. Notice that the HOUR, MINUTE, and SECOND functions are used to extract values for the Hour, Minute, and Second arguments. The TIME Function puts these into a nicely formatted time value of 6:28 PM.

Now the Time values can be used independently of the Date.

Note: You don’t see the 30 seconds in cell B2 because of the default cell formatting for Time in my spreadsheet.

A Shortcut Formula [UPDATE]

I had a reader comment about an easier formula so I will include it in this post. Thanks JMarc.

A Date-Time serial number, with General formatting, shows up as an Integer.Fraction. You don’t normally see this on your spreadsheet as Excel will automatically format Date-Time numbers as, well, Date-Time numbers.

The screen shot below shows the Date-Time number and its serial number equivalent with General formatting. Both underlying numbers are identical, the cell formatting is the only difference.

The INT function returns the integer portion of a number. If we use the Date-Time number and subtract the integer portion, that leaves us with the fractional portion, which is the Time. The formula =C2-INT(C2) will return the number 0.769795602.

Reformatting the cell to a Time format will allow the fraction to show as a Time value.

Changing the cell formatting gives us the time value 6:28 PM.

Extract a Date from a Date-Time Number in Excel

I recently saw a spreadsheet with the following function =LEFT(A2,8) where cell A2 was equal to 08/06/12 12:23 PM. The user was trying to extract the Date from the Date-Time value. The problem for me was that the LEFT function returns a “text” value. Excel is good at recognizing text values that look like dates, as Dates, but why not use a formula that returns a numeric value?

My reflex was to enter the following formula =DATE(YEAR(A2),MONTH(A2),DAY(A2)) and get a proper Date value. You can tell the difference because, with no cell formatting, a Text value is left-justified in the cell, and a Date value is right-justified in the cell, meaning that it’s a number.

I got to thinking later that there’s a simpler formula to extract the Date from a Date-Time value, which is =INT(A2). After you enter this formula, the cell formatting needs to be changed to a Date format.

Why Does the INT Function Extract a Date?

All Dates, Times, and Date-Times are know as serial numbers in Excel. You don’t normally see the underlying serial number. A Date-Time value like 9/14/2012 8:43 AM looks like that in the cell AND in the formula bar.

Date Time shown in Cell and Formula Bar

Only when you change the cell formatting to General do you see the Date-Time serial number. The integer portion is the date serial number and the fractional part is the time serial number.

Serial Date Time

This is why the INT function will work to extract a Date from a Date-Time value. The integer value is a date serial number. The trick is to change the cell formatting to a Date format so that Excel will show you the Date.

Format Cells Date

Add Macro Button to the Toolbar in Excel 2011

You can add an icon to the toolbar in Excel 2011 for your Personal Workbook Macro. In an earlier post I created a short macro to imitate the Control+Home keyboard shortcut in Excel for Windows. You can add an icon to the toolbar to run that, or any other macro with a few quick steps.

Right click on the toolbar and select Customize Toolbars and Menus… then
Click the Commands tab, then
Scroll down and select Macros from the Categories pane, and
Drag the Custom Button with a smiley face to the toolbar, then
Click OK to get rid of the dialog box.

Next you:

Right click on the smiley face, select Assign Macro… and

Pick the macro you created (in your personal macro workbook), then

Click OK.

Change the Smiley Face Image

If you don’t like the smiley face icon staring back at you want to change it (like I did) that’s easily done as well.

Right click on the Custom Button icon and select Properties… then

Type a name in the Name: box, then

Click the drop-down arrow beside the smiley face and select from the icon list, then

Click OK.

Use a Custom Image for the Toolbar Button

The icon list is rather short and pathetic, but you need not restrict your choices as Excel allows you to copy and paste an image into the button face. Here is how I changed my smiley face image.

Choose Insert > Symbol

Click Shapes (at the top of the dialog box)

Right click on your favorite shape and click Copy, then

Close the Media dialog box, and

Right click on the smiley face toolbar icon, then

Click Properties…

Click the drop-down beside the image, and

Click the option Paste Button Image, then

Click OK.

Move Your New Macro Button

In case you didn’t get your button located in the right place on your toolbar when we first started this exercise, the button is easily moved.

Right click on the Toolbar and select Customize Toolbars and Menus…

Drag your icon to a position of your choice, then

Click OK.

Control + Home in Excel for Mac

One of my all-time favorite keyboard shortcuts in Excel is CTRL+Home, but on a Mac there is no Home button. Hence a constant source of frustration these last two years.

I finally decided to do something about that and recently figured out a solution using VBA and the Personal Macro Workbook. But before I go straight to the answer, let me tell you how I got there.

My Journey

I knew that VBA was going to enter into the equation, so I started to record a macro on a Windows PC while using the Control+Home keyboard shortcut. What I found out is that Excel does not record that keyboard shortcut. Nothing, nada, zip.

I then noticed that Control+Home acted differently, depending if the sheet panes were frozen or not. When sheet panes are not frozen, the Control+Home shortcut took me to cell A1. When the sheet panes are frozen, then the upper left corner cell of the window was selected.

To make a long story short, I found out that the active window’s scroll row and column were being selected when the CTRL+Home shortcut is executed in Excel for Windows.

The Control+Home Macro

I also knew that if a Chart sheet were selected the macro would fail, so I crafted the following macro to mimic the CTRL+Home shortcut on a Mac. (Works on a Windows PC too.)

Sub GoHome() ''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Make sure the active sheet is a worksheet, ' then locate the active window's scroll row and ' column, and activate that cell. ''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim lngRow As Long Dim lngCol As Long

If ActiveSheet.Type = xlWorksheet Then lngRow = ActiveWindow.ScrollRow lngCol = ActiveWindow.ScrollColumn Cells(lngRow, lngCol).Activate End If End Sub

The next thing I did was create a shortcut for the macro, but realized that the macro would only work with the current workbook. Rats!

I wanted the macro work automatically on every Excel file so I chose to store this macro in the Personal Macro Workbook, which solved the problem.

However the shortcut key combination I assigned did not work in Excel for Mac. There was a conflict. It seems that COMMAND+OPTION+H is a reserved keyboard shortcut for the Mac. I found out this shortcut hides all windows except the one that is active.

How I Created the Control+Home Keyboard Shortcut on my Mac

Here are the steps I took, with a few false starts along the way.

Open Excel 2011 and choose Tools > Macro > Record New Macro…

Type a name for the macro. I used GoHome.

Enter a Shortcut key. (I used h, which didn’t work so I changed to g as you’ll see below.)

Where you see Store macro in: click the drop-down and select Personal Macro Workbook.

Click OK.

I got a warning that the keyboard shortcut Option+Command+h was reserved.

So I chose g instead and clicked OK.

Next choose Tools > Macros > Stop Recording. This will effectively end the macro recording without recording anything.

Choose Tools > Macro > Macros…

Click the Step Into button on the Macro dialog box, which will take you directly inside the macro in the VBA Editor. (Note: While this takes you directly to the macro, it also starts the macro running inside the VBA Editor.)

Click the square Reset button to stop the macro program execution. (Note: If you know how to navigate the VBA editor, you can skip this last step and choose Tools > Macro > Visual Basic Editor and then locate the macro.

Select the entire GoHome code from this article. Or click this link and copy from a new window.

Copy the text,

Switch back to the Excel VBA editor,

Select the entire GoHome subroutine, and

Paste the code.

Close the VBA editor by choosing Excel > Close and Return to Microsoft Excel.

Now here is the important part so pay attention. You have to save changes to the Personal Macro Workbook. You will be asked to do this when you Quit Excel.

Choose Excel > Quit Excel and the following dialog box will appear.

Click the Save button when asked, “Do you want to save the changes you made to the Personal Macro Workbook?”

Run the GoHome Macro

Now lets check it out. Open Excel 2011 and select any cell that is not A1, then use the shortcut (mine was Command+Option+g) and watch the active cell change to cell A1.

If your shortcut doesn’t work you can set it now. Choose Tools > Macro > Macros… and select the GoHome macro, then click Options. Type in a shortcut key and click OK.

Now choose any cell in the top left quadrant of the current window, like C5. Choose Window > Freeze Panes. Next select any cell except C5 and run the shortcut combination for the macro, and watch the curser jump to cell C5.

The Personal Macro Workbook will load each time you open Microsoft Excel so it’s always in the background, and you don’t get that annoying “enable macro” pop-up screen.

A Dynamic Dependent Drop Down List with a Horizontal Table Reference

I received a comment asking if a dynamic dependent drop-down list in Excel could have a list where the “table headers were actually rows and not columns?” Since I’ve already detailed how this is done in the article mentioned above, I’ll keep this short. The screen shot below is what I’ll be referencing. At the end of the post I’ll give a link to the file I used.

Conditional Drop Down List (Excel)

There are two named ranges,

myCategoryH

myTableH

that refers to range F1:G3.

A defined name, myItemListH, is created with the following formula =INDEX(myTableH,MATCH(Sheet1!A2,myCategoryH,0),0) that will return a row that is matched by the contents of cell A2.

In cell A2, add a Data Validation list with the source being =myCategoryH. In cell B2, add a Data Validation list with the source being =myItemListH in the conditional drop down list from Excel.

Now you’re done.

Cell A2 will give you a drop-down list of Fruit, Vegetables, or Other Stuff. And cell B2 will read the value in cell A2, match and return the proper row number, and return an array of values for that row.

Two Ways to Use the INDEX Function to Return an Array

This is a simple case of using the INDEX function in a slightly different way. Normally, to return column 2 of a named range, you would use the following formula =INDEX(myTable,0,2). The zero means: return all rows (of column 2).

If you want to return row 2 of the named range you would use =INDEX(myTable,2,0). Here the zero means: return all columns (of row 2).

This comes from the Help section of the INDEX function where in Excel 2011 it reads:

If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells for a row, and in a vertical range of cells for a column. To enter an array formula, press ⌘+RETURN.

Practical Considerations

Adding to each category list across columns is problematic. For one thing adding more data to the table will require inserting a column within the table range to avoid renaming the range. Obviously a standard Excel “Table” won’t work with this type of list. And if you have a very large spreadsheet the number of columns will become limiting long before the number of rows would.

Download the File

Horizontal-Dynamic-Dependent-Drop-Down-List.xlsx

How to Update a List or Range without OFFSET

I avoid the use of Volatile Functions, especially OFFSET, which is commonly used to update a list or range. They can slow down the operation of your workbook. For very large workbooks with lots of data, it can be significant and irksome.

Worksheet cells that use Data Validation for a drop-down list can simplify the input process, or be used to limit the available choices. But the list needs be expandable. Here are two primary ways to keep your data validation list automatically updated, without having to resort to using the OFFSET function.

Update Your List Range with VBA

Put your data validation drop-down cell on one worksheet and the reference list range on another worksheet. Example: Sheet1 contains a cell with data validation. Sheet2 contains a data range (the list) that is given a defined name of myList. Add some VBA code in the Sheet2 Deactivate routine to update the named range.

Private Sub Worksheet_Deactivate() Dim rng As Range Set rng = Sheet2.Range("myList").CurrentRegion Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count) rng.Name = "myList" End Sub

This is an event-based programming technique, which I commonly use with Excel 2003.

Data Validation List Lookup 1 Sheet

Use Some Table INDEX Magic

This is a variation of the same thing, but no VBA programming is warranted. Instead, use a Table for your reference list data. Then create a defined name with the INDEX Function, and use that name for the data validation list.

Tables automatically update their ranges when expanded and the INDEX function will too. Example: Create the defined name myListFormula and use =INDEX(Table1,0,1) as the formula. Then when setting the data validation list, use =myListFormula as the list reference.

Data Validation List Lookup 2 Sheet

Example Worksheet

I’ve put together a workbook with the two examples listed above. The first technique, with VBA, uses two worksheets: Lookup 1 and Data 1. The Data 1 worksheet has the VBA code, which updates the named range when deactivated. You can add or subtract to this list and the data validation list on the Lookup 1 sheet will automatically be updated.

Both Lookup sheets have data validation in cell A2, which is a list of names. I’ve added another column for the city that uses a formula to get the right value from the list.

Lookup 1 Sheet

Data 2 Sheet with VBA Code

The second example uses Lookup 2 and Data 2 worksheets. The Table is on the Data 2 worksheet. When you add or subtract data from this Table and the defined name myListFormula will automatically update the data validation list on the Lookup 2 worksheet. Be sure to look at the Define Name dialog box (on the Mac) or the Name Manager (Windows) to see the INDEX formula.

Define Name Dialog Box

Download the file: Data_Validation_List_Update.xlsm

Since there is no OFFSET function, updating at random intervals, in either of these examples, I can rest easy. That’s one less thing to slow down your worksheet.

Select Excel’s Used Range on a Mac

I recently read a good blog post over at Contextures about selecting the actual used range on an Excel sheet, both manually and with VBA. However, using Excel on a Mac makes you keenly aware that there’s no Home button.

The used range on a worksheet starts with cell A1 and ends with the last used cell in the worksheet. This “last cell” is not always apparent, but easily found. Just use the keyboard shortcut CONTROL + G to bring up the Go To dialog box.

Click Special… which will bring up the Go To Special dialog box.

Select Last cell and click OK.

The last cell may sometimes surprise, because Excel considers cell formatting as being “used” so you may see blank cells that are way outside your data range. Tip: Sometimes you can delete the seemingly extra rows and columns outside your data range and it will reduce the file size.

Select the Used Range by Navigating Back Home

Once you find the last cell, you can then hold the Shift key down and click cell A1 to select the entire range. Of course if you can’t see cell A1 in the current window there is no Home button on the Mac to help you out. (Major bummer)

The next best thing is to hold the COMMAND + Shift keys down while you tap the left arrow and up arrow keys until you reach cell A1. This can be simple, or time-consuming depending upon size and shape of your worksheet.

Selecting the Used Range

To select the entire used range with VBA is a simple matter. Choose Tools > Macro > Visual Basic Editor, then choose View > Immediate Window, and type activesheet.usedrange.select inside the immediate window and hit enter.

Create a Macro to Select the Used Range

You can also create a macro to select the entire used range by opening the VBA Editor, inserting a Module, and entering the following code.

Sub ActiveSheetUsedRange() ActiveSheet.UsedRange.Select End Sub

Or you could get fancy with this code.

Sub SelectUsedRange() Dim rng As Range Set rng = Range("A1").SpecialCells(xlCellTypeLastCell) Range(Cells(1, 1), rng).Select End Sub

Add a Keyboard Shortcut for the Macro

To make things simpler to run the macro, you can enter a shortcut. Just choose Tools > Macro to bring up a dialog box.

To add a shortcut, select a macro and click Options… which will bring up the Macro Options dialog box.

Enter a shortcut key by clicking inside the Shortcut key box and pressing a key on the keyboard. I pressed the “u” key on the keyboard, and consequently the keyboard combination is shown as Option + Cmd + u, as you can see in the screen shot. (Depending on the key, you may also include COMMAND, Control, Shift into your shortcut.)

Enter a description if you wish, and then click OK. Select the Cancel button on the Macro dialog box to make it disappear.

Now you can run the macro by simply using the keyboard shortcut Option + Cmd + u.

Note: This macro will not work if a Chart sheet is selected.

Used Range verses Actual Used Range

The actual used range might be different than the used range. Meaning that some blank cells that are formatted might be included in the used range. Most likely you will only want to deal with a range that has some actual values. This would be the actual range.

Please refer to the aforementioned blog post over at Contextures to see a couple of different examples of code that you can use to select the actual used range. These examples are short and use the VBA FIND function to get the job done.

Pay Periods and Funky PivotTable Controls

I like to use a PivotTable to figure out simple problems in Excel. So for this post I’m going to use Excel 2011 (Mac), where PivotTable controls look funky when compared to their Windows counterpart.

Since I get paid every two weeks, certain months in a year will contain three pay periods. Planning future vacations during these months isn’t a bad idea, so I’m going to look at pay periods for the next three years.

Add a Column of Dates

Two Week Pay Periods I’ll enter the first pay period, then create a formula that adds 14 days and copy it down to get my date range.

Since a PivotTable will “see” the underlying serial date, I’ll need to add another column for Month and give it a “Month-Year” format so the PivotTable will group similar Months together. For this I’ll use the TEXT formula. The “Pay Period” Date is used for the first value argument, and then “mmm-yy” for the format_text argument.

So the formula in cell B3 =TEXT(A3,”mmm-yy”)

As you can see in the screen shot, the months Dec-11 and Jun-12 have three pay periods. A PivotTable will quickly summarize more than one year and show the number of times a pay period happens each month.

Add a PivotTable

The steps to create a PivotTable in Excel 2011 are as such.

Select a cell inside the data range
Click the Data tab on the Ribbon
Click the PivotTable drop-down arrow and select Create Manual PivotTable…

Create Manual Pivot Table

On the PivotTable dialog box, click OK

Create PivotTable Dialog Box

You’ll get a new worksheet that shows an empty PivotTable Layout. There’s an introductory PivotTable popup box that has a link to Learn more about PivotTables, which brings up the Help system topic About PivotTables. Click the x to dismiss this help box.

PivotTable Help Dialog Box

The PivotTable Builder box is also shown. This object looks quite a bit different from the traditional Windows counterpart. My first reaction was that it looks funky. Nevertheless, it’s the functionality that counts.

Arrange the PivotTable Layout

Click and drag Month from the Field name area to the Row Labels area. Then click Month again and drag it to the Values area. (Yes that’s right, you’re dragging Month twice.)

PivotTable Builder Month

In the Values area you should see Count of… and to see the rest, just click the i to bring up the field name list.

PivotTable Field Dialog Box

Sort the PivotTable

Click inside the Data area (like cell B5) of the PivotTable and then select Descending from the Sort icon drop-down list on the Toolbar.

Descending Sort

The top of the list shows months with 3 pay periods. Just what I was looking for.

Sorted PivotTable

You’ll notice the descending sort doesn’t leave the Row Labels in ascending order. (Nov-12 doesn’t follow Jun-12, etc.)

A Better Formula

You can change the Month formula to =TEXT(A2,”yyyy-mm”) and the Row Labels will show up in year-month format in ascending order.

PayPeriod Sorted Month Ordered

While this took some time to explain, the reality is when I do this it takes about two minutes. And the bulk of the time is generating the dates and adding the formula.

Fill Down a Formula with VBA

I commented on a post that brought to light, the fact that, using the cell fill-handle to “shoot” a formula down a column doesn’t always work when the adjacent column(s) have blank cells. So I decided to share some Excel VBA code that’s used to copy a formula down to the bottom of a column of data.

The situation is depicted below. Cell C2 is active, and has the formula =B2+A2. I want to copy it down to the rest of the column in this data range. However, cells B6 and B11 are empty, along with countless others below the visible table range. (Pretend this data table is huge.)

Test Formula Fill Down BEFORE

Here is some VBA code that will Fill Down the formula.

Sub FillDownFormula() ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Filldown a formula for in column of data. ' Assumes a data table with headings in the first row, ' the formula in the second row and is the active cell. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim rng As Range Dim rngData As Range Dim rngFormula As Range Dim rowData As Long Dim colData As Long


'   Set the ranges

    Set rng = ActiveCell

    Set rngData = rng.CurrentRegion
'   Set the row and column variables

    rowData = rngData.CurrentRegion.Rows.Count

    colData = rng.Column

' Set the formula range and fill down the formula Set rngFormula = rngData.Offset(1, colData - 1).Resize(rowData - 1, 1) rngFormula.FillDown End Sub

The key is setting the formula range (rngFormula). I take the entire region of data (rngData) and offset by 1 row because I don’t want the header row, then I resize the rows in the range by subtracting 1 from the total number of rows because I now need one less row in the range.

Next I offset the entire range by the row number of the active cell, but have to subtract one column because I offset from column 1, not column 0. And finally I resize the data range to 1 column, which gives me the single-column range I want with the formula in the top row.

The routine does no error checking and is restricted to using the active cell that has a formula. But it does the trick given those limitations.

A Dynamic Dependent Drop Down List in Excel

The other day I was reading a post over at the Contextures blog about Dynamic Dependent Excel Drop Downs and realized that using an Excel Table would provide an alternative method that is both simple and flexible. Tables are available in Excel versions 2007, 2010, and 2011.

In this post I’ll create a Table to hold the Category’s and Items, create three defined names using dynamic formulas, then use Data Validation to create two drop-down lists, the second being dependent upon the first.

Create at Reference Table

Here’s a Table with Category names in the header row and Items in the columns. I just typed in the information then converted to a Table.

Create a Dynamic Defined Name for Category List

Create a defined name for the Table1 Header row range by using the formula =Table1[#Headers]. Please note that Table1 is the name of the Table created in the step above.

This defined name is dynamic, meaning it will expand when more columns are added and shrink if any columns are deleted. It will return the header row of the Table, which we’ll use in the next step. I used myCategory for this defined name.

To create a defined name in Windows choose Formulas > Name Manager then click New…. On a Mac choose Insert > Name > Define…. This will bring up the New Name dialog box that looks like the Edit Name screen-shot shown above.

Create a Category Drop Down List with Data Validation

Type Category in cell A1 for the column heading. Next select cell A2, then choose Data > Data Validation > Data Validation… and in the Data Validation dialog box (shown below) select List from the Allow box, then type in =MyCategory in the Source box, and click OK.

Now cell A2 has a drop down button that shows the Header row of the Table. (Don’t worry about extending this Data Validation down to more rows, we’ll take care of that later.)

Column B will hold a drop-down list for the Item, which is dependent upon the Category. This takes two defined names to work properly.

Create the First Defined Name for Items

[Update: Select cell B2 before you follow this next step.]
Create a defined name with the following formula:

=INDEX(Table1,0,MATCH(Sheet1!A2,Table1[#Headers],0))

that I’ll name myItemList.

This formula will return a reference to the Table column that matches the Category selection in cell A2 on Sheet1.

If I use Data Validation to create a drop-down list with the myItemList defined name I’ll get eight items returned because Table1 has eight data rows. As you can see in the picture below, the Vegetables item list has two blank lines, and the Other Stuff item list has one blank line. Not an elegant solution.

However, if you have a table that always has equal items (rows) in each column then this defined name formula will will work well for a Data Validation list.

Create a Second Defined Name for Items

We can create a second defined name that will give us a dynamic list with the exact count. The following OFFSET formula will do the trick.

=OFFSET(myItemList,0,0,COUNTA(myItemList),1)

This formula uses the first defined name (myItemList) but alters the height by counting the items. I named this defined name myItem, as you can see below in the Name Manager screen shot.

Create an Item Drop Down List with Data Validation

Type Item in cell B1. Select cell B2 and open the Data Validation dialog box. Choose List and enter the following formula =myItem, then click OK.

The result is a dynamic drop-down list in the Item column that’s dependent on the Category selection in column A, and returns the exact list.

Convert to a Table

Now it’s time to covert this to a Table, and by doing so the Data Validation will be preserved and automatically expand with the addition of more rows.

Select cell A2, then in Windows (Excel 2007, 2010) choose Insert > Table, verify the information in the Create Table dialog box, and click OK.

On a Mac (Excel 2011), select cell A2 then choose Tables > New > Insert Table with Headers.

At this point I normally turn off the Data Filter because it’s rather annoying.

As your Table expands, with more rows or columns, this dynamic drop-down list will work just fine. To create a new row in the Table with Data Validation press the Tab key while the active cell is the last column of the last row.

Potential Problem with OFFSET

As you might have guessed the OFFSET formula depends on having items at the top of the list. Should your data have blank rows in the middle of the column, the drop-down won’t have all the items listed yet show blank cells in the list.

In this case it’s best to use the first defined name (myItemList) in creating a dependent, drop-down list for Item. At least you’ll get all the data, even if it does have some blanks in the list.

Reference Table Location

For illustration purposes I’ve shown the reference Table on the same worksheet as the Category and Item Table. Normally I would place the reference Table on a different worksheet. In this instance none of the formulas would change.

[UPDATE]

I’ve had numerous questions about the details of this post so I’m putting a link here to download the file.

Dynamic Table Reference with INDEX

Tables have structured data so you can easily use column references in formulas. But with the Table name AND column name, the reference can be rather lengthy and hard to understand at first glance.

As an alternative, you can simply create a dynamic column reference in a Table by using the INDEX function in a Defined Name.

First I will explain how the INDEX function works to capture a dynamic Table range, then I’ll create a Defined Name using the INDEX function, use it in a formula, and see what happens when things change.

The Dynamic INDEX

The syntax is INDEX(array, row_num, [column_num]) where array refers to the Table name, the row_num reference is set to zero (0), and the column_num is a number of the Table column you want to reference.

Using zero (0) for the row_num argument forces all rows in the column to be returned.

Create a Defined Name Formula with INDEX

I’m going to use an example for the column Number Setups in Table1.

Table1

In Excel 2010 and 2007 (Windows) choose Formulas > Name Manager and click New. In the New Name dialog box type nSetup. In the Refers to: box type the formula =INDEX(Table1,0,4) then click OK. You can also omit the zero for an equivalent formula =INDEX(Table1,,4).

This next example will use the Table1 column Setup Hours. In Excel 2011 (Mac) choose Insert > Name > Define. In the Define Name dialog box type sHours into the Names in workbook box. In the Refers to: box type the formula =INDEX(Table,1,5) then click OK.

I have an existing formula for Average Hours per Setup using structured Table references that looks like this:

=SUBTOTAL(109,Table1[Setup Hours])/SUBTOTAL(109,Table1[Number Setups])

Using the Defined Names we create a formula like this:

=SUBTOTAL(109,sHours)/SUBTOTAL(109,nSetups)

Both formulas are equivalent, but the second is shorter and a bit more clear (in a techno-geek kinda way).

Changes to Be Aware Of

If you change the name of the Table, Excel will modify everything related to the name so there are no worries about the Define Name formula. I changed the Table name to i and here’s what the Defined Names look like.

If I move the Table to some other place in the worksheet, there’s no effect to the Defined Name formulas because the column references are for the Table and not the worksheet.

However, if you move the column location within the Table, the Defined Name will not change its reference to the new column position. The column reference has to be manually changed in the INDEX function within the Defined Name.

Conclusion

The moral of this story is that Defined Names using the INDEX function to return a dynamic range reference in a Table can be used to make formulas easier to read, but moving a column within a Table can invalidate the reference.