Category Archives: Advanced

Advanced level information

how to make a scatter plot in excel

Ready, Set, Scatter: How to Make a Scatter Plot in Excel

This article consists of all the basics of how to make a scatter plot in Excel. By using this guide, you will be able to generate your own plots as well as format them and add design features.

Scatter plots, also known as scatter charts or XY scatter plots, are a powerful visualization tool for your data. They are used by engineers, statisticians, and other scientists to demonstrate visually a relationship between two variables using an XY axis chart.

What is a Scatter Plot?

Before we dive into how to make a scatter plot in Excel, we must first answer the question ‘what is a scatter plot?’ While they may sound complicated to make and use, they are similar to line graphs in many ways, particularly in that they use horizontal and vertical axis to plot out data points. Scatter plots indicate how a variable is affected by another. We call the relationship between the variables the correlation.

Generally speaking, scatter plots contain a wide variety of data. When the two variables are listed close together, this indicates the strength of their relationship. When the variables make a straight line out from the point of origin to high x and y values, they have a positive correlation. The inverse is also true. Variables that go down from a high value on the y-axis to a high value on the x-axis have a negative correlation. A perfect positive correlation has a value of 1. Similarly, a perfect negative correlation has a value of -1.

how to make a scatter plot in excel

Relationship Between Variables

A classic example of the relationship between two variables is the one between height and weight. By collecting height and weight data on 100 high school boys, for example, you can then use a scatter plot to demonstrate a relationship between a boy’s height and weight.

The taller the boy, the more likely he’ll weigh a little bit more. Of course, there are some outliers. We all have that really tall, skinny friend, but he will also be taken into account in the scatter plot.

Of course, if you’re looking at this article, chances are you already know a little bit about statistical relationships and scatter plots. What you want to know is how to go about visualizing the data you already have in a neat-looking and comprehensible chart in Excel.

Not all data can be visualized well using a scatter plot, especially if you want a plot that utilizes a great deal of text. You will have to play around with the data you have to figure out whether a scatter plot is appropriate.

Nevertheless, in this article, we will discuss exactly how to make a scatter plot in excel, so you can determine if it shows the information you want to show.

The Data

The first thing you will need is to compile your data. For the purposes of this article, we will use the height/weight example to demonstrate how to make a scatter plot in Excel. As you can see from the screenshot, we have three columns: person, height, and weight.

name, height, weight, how to make a scatter plot in excel

Insert Your Chart

Now that we have the data we need, we will go ahead and insert a chart. To do so, go to the “INSERT” tab and look for the “Charts” menu. There are many different charts you can create using Excel, including a line chart, pie chart, and a column chart.

For now, though, we will stick to the scatter chart, although it is good to know where the other charts are. That way, if a scatter chart doesn’t work for your data, you can easily return to the INSERT tab and try another one.

When you click on the scatter chart button, a drop-down menu will appear. The menu presents five different types of scatter chart you can use.

The five different types of scatter chart are:

  • 1st Scatter
  • 2nd Scatter with smooth lines and markers
  • 3rd Scatter with smooth lines
  • 4th Scatter with straight lines and markers
  • 5th Scatter with straight lines

Each of these charts will present the same relationships, but they will look different. The most recent versions of Excel will show you previews of the charts when you hover the mouse over a specific selection. For example:

how to make a scatter plot in excel

As you can see here, our data has been separated into two lines: the blue for height, and the orange for weight. However, there are a few things we might want to change.

For example, at the moment, the X-axis shows the number of students. This kind of thing is fine, especially if you are showing how a dataset can change over time.

But we want to show a relationship between height and weight instead. To do that we simply select the data differently. Now our chart shows height on the X-axis and weight on the Y-axis.

how to make a scatter plot in excel

You can see here in that now we have only blue dots, but they represent the relationship between height, which appears on the X-axis, and height, which appears on the Y-axis.

Adding Design Elements to Your Scatter Plot

Now that you have a scatter plot, you can play around with it. There are many different flourishes that you can add.

One easy thing you could do to this example is to add titles to your axes. As you can see above, none of our axes are named. You have the data right in front of you, so you know what is on the two axes, but other people who are only able to see your scatter plot might not.

By selecting the chart, buttons will appear on the right-hand side, which will allow you to adjust or change design elements. The specific buttons are chart elements, style, and values. To change the axis titles just click the elements button:

how to make a scatter plot in excel

As you can see, there are many different elements you can add to your scatter plot. You can change the title, add a legend, and include a trend line. This will make it easier for your viewers to follow the relationship you are trying to prove.

The Design and Format Tabs

If you click on the “DESIGN” tab you will find even more features. They can help you create the exact design you want. You can change the color of your chart, the scale, whether or not the chart is gridded. You can play around with any design elements you want to see what fits for your specific presentation.

“FORMAT” on the other hand allows you to change things like font style, shape style, and other elements.

How to Make a Scatter Plot in Excel: Final Review

Microsoft Excel has some powerful features that allow its users to present data in many different ways. When you are trying to present relationships between variables, a scatter plot can be a very useful tool. You can choose between numerous kinds of scatter charts to best fit. In addition, once you have chosen and created a chart to present your data. You can then manipulate your plot in any number of ways to better show off that data. You can change myriad design elements to fit the particular needs you or a client has.

how to alphabetize in excel

How to Alphabetize in Excel: Fast & Simple

how to alphabetize in excel

One of Excel’s most important features is the ability it gives users to quickly and easily sort through data. With this said, learning how to alphabetize in Excel is critical. If you are not familiar with this skill, don’t worry too much. We will go over alphabetizing lists of strings, ordering numerical values, standard alphabetization, reverse alphabetization, and more. Using Excel we can properly organize data for our businesses or personal finances. Learn how to alphabetize in excel so it is easy to read and refer back to whenever necessary.

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.

products how to alphabetize in excel

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.

customers table how to alphabetize in excel

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.

sort how to alphabetize in excel

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.

sort 2 how to alphabetize in excel

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.

products table 3 how to alphabetize in excel

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.

products table 4 how to alphabetize in excel

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:

custom lists how to alphabetize in excel

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.

products table how to alphabetize in excel

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.

how to make a drop down list in excel

The Drop Down Basics: How to Make a Drop Down List in Excel

This article shows you how to make a drop down list in excel.

It provides two ways for you to quickly and easily make drop down lists in an Excel spreadsheet.

You can follow the instructions to create a new drop down list that will help streamline efficiency when collecting information in your spreadsheet.

A dropdown list can be a useful thing in your spreadsheet. Especially if you want to make it easier for others to enter information on your sheet quickly.

With a dropdown list, you can give other users a quick set of options instead of having to type in each piece of information in every cell.

It is also helpful for the person responsible for the spreadsheet because it standardizes the data you want to collect, which may cut down on confusion or problems when it’s time to inspect your data or make calculations.

This article will go through a step-by-step process of how to make a dropdown list in Excel.

We will show you:

  • where to find the dropdown feature
  • how to enter a list of options that will appear in your dropdown list
  • and even how to use a dropdown list that you have in a different spreadsheet

Let’s get down to business and learn how to make a drop down list in excel.

How To Make A Drop down List In Excel: Getting Started

Today we are going to use an example of a group of friend’s favorite types of fruit. There are two ways you can do this.

The first involves making one spreadsheet. The other requires you to make two.

For the method involving two separate sheets.

The first spreadsheet, which will be the one where you will eventually insert your dropdown list, will contain the list of all the friends.

The second sheet will be where you create the possible selections of favorite fruits that will make up the dropdown list.

Excel sheet with names and favorite fruit how to make a drop down list in excel

Here is the list of people who will be taking the survey:

list of fruits how to make a drop down list in excel

And here is the sheet containing the list of fruits that the participants can select:

Now that we have our two spreadsheets set up, we can go about making the list of fruits in Sheet 2 into a dropdown list that users with access to Sheet 1 can select.

Finding the Dropdown Feature

The dropdown feature is not the easiest feature to find in Excel. However, with our help, you’ll be an expert in no time.

In Excel, you must scroll to the “DATA” tab in the menu.

Be sure you are currently on the sheet that you want your dropdown list to appear. In the section named “Data Tools” you will see the feature for “Data Validation” which should look like this:

excel menu how to make a drop down list in excel

Once you have found the button, begin by selecting the cell, or in this case the range of cells, you want the list to appear in.

Then click the data validation button.

After that, a dialog box will appear with several options.

Under the “Allow” box, click the dropdown menu and select “List,” which will look like this:

validation criteria how to make a drop down list in excel

After selecting “List,” you will then tell the spreadsheet what source you want to use, in this case, the list of fruit we made in Sheet 2.

data validation how to make a drop down list in excel

Note that in the “source” line there is now a somewhat complicated looking formula. That’s just telling spreadsheet 1 to look to Sheet 2 to find the items that will be in your dropdown list.

Once you press enter, you will be taken back to spreadsheet 1 with the names of the people taking the survey.

In the column underneath the “FAVORITE FRUIT,” the cells now have a dropdown list. You’ve now made a simple dropdown list for your spreadsheet.

favorite fruit drop down how to make a drop down list in excel

If you want to do all this in a single spreadsheet you will follow some of the same steps for setting up spreadsheet one, but don’t need to make a second sheet.

Instead of pointing your dropdown list to Sheet 2, when you fill out the “source” in the data validation box, you type in the possible options for the dropdown box.

The one-sheet method is useful when you have a limited number of responses, for example, if you are gauging attendance for an event and your only answers are attending/not attending.

But, as in our example, if you have a long list, it’s much easier to make everything on a separate sheet. Using two sheets will also make it easier to modify the list later.

More on Dropdown Lists

Now that you have made your dropdown list, there are a few things that you need to decide on. And you also have a few things to check.

First, if you only want the users to see one spreadsheet, you can hide the other spreadsheet by right-clicking on the tab. And then selecting “hide” from there.

However, if you do it all on one sheet other users could make changes to the dropdown list. Thus, if you are the only one who wants to be able to control what is on the lists. It’s better to follow the two sheet method.

You can also protect the spreadsheet or choose to lock it down so that no other user besides yourself can make changes.

One thing to check on is making sure that the cells are wide enough to display the whole entry.

In our example above, all of the words are relatively short and this isn’t a problem. But it is always a good thing to check before you send your spreadsheet to a bunch of people.

Modifying Your Dropdown List

Maybe you forgot to add your own favorite fruit, or just want more options. Don’t worry. It is quite simple to add items or remove them, from your dropdown list.

If you have made your dropdown list with two spreadsheets, which we feel is the better method, all you have to do is go to that sheet and add the items that you want to be included.

The dropdown list will then automatically be updated in the other spreadsheet.

Similarly, if you want to delete an item you just go to the sheet. From there, you delete whichever items you no longer want to include in your dropdown list. Easy as that!

If you created a dropdown list by manually entering the options, you just have to go into Data Validation again. And then enter the new items to the list from there.

Conclusion

You now know how to make a dropdown list in Excel!

Dropdown lists are useful any time you want users to enter specific data that you have already identified. It can keep things uniform and is more efficient for other users who are entering data.

Analysis ToolPak AddIn

Check the Analysis ToolPak Add-In in Excel 2003 [VBA Code]

Analysis ToolPak AddInI recently began using the NETWORKDAYS function in Excel 2007, yet my file was going to be widely used by people with Excel 2003 (both are Windows versions). The NETWORKDAYS function is a standard function in Excel 2007, but with 2003 you have to install the Analysis ToolPak, otherwise you get an error.

The only way for me to avert an error with users who don’t have the Analysis TookPak installed in Excel 2003 is to use some VBA code that runs when the file is opened. I want to see if they are using a version of Excel older than 2007, check if they have the Analysis ToolPak installed, and then tell them, with a pop-up message, to install the Analysis ToolPak, so all the formulas will work correctly.

Create a Routine to Check for the Analysis ToolPak

Open the Visual Basic Editor (Alt+F11) then choose Insert > Module and enter the following VBA code.


Sub CheckToolPak()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Checks to see if the Excel version comes before Excel 2007,
' if so then checks to see if the Analysis ToolPak is installed.
' If not, then notify the user and end the program.
'
' This is required because of the NETWORKDAYS formula
' that is used in the charts.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If CInt(Application.Version) < 12 Then If AddIns("Analysis ToolPak").Installed <> True Then
MsgBox "Please install the Analysis ToolPak." & vbCr & vbCr & _
"Choose Tools > Add-Ins... " & vbCr & _
"then check the box for Analyiss ToolPak, and click OK."
End
End If
End If
End Sub

The code is somewhat self-explainatory except that Application.Version returns a text value, hence you see the CInt function that converts that text value to an integer so that we can tell if the Excel version is less than 12, which is the version number of Excel 2007.

Create a Workbook Open Event

This will do the trick, but needs a trigger to call the routine. So double-click ThisWorkbook in the Project Explorer of the VBA Editor, then select the drop-down at the top-left of the window – where you see (General) – and select Workbook. The Private Sub Workbook_Open() subroutine will appear with no code.

Type in Call CheckToolPak and then save the file.


Private Sub Workbook_Open()
Call CheckToolPak
End Sub

Now when you open the file with Excel 2003, and don’t happen to have the Analysis TookPak installed, you get the following message.

Analysis ToolPak PopUp

If users have the Analysis ToolPak Add-In installed in Excel 2003 there is no message, nor if the user is using Excel 2007, 2010 or 2011.

Select a Column of Non-Sequential Data

Non-Sequential Data

In one of my posts on using VBA to select a column of data, I received a comment asking if the macro could be modified to select data like the screen shot you see to the left.

This data is non-sequential and consequently the CurrentRegion property won’t work. The way around this is to select the very last row in the same column, then shoot up (Ctrl + Up Arrow) to find the last data cell.

Once you know the where the column heading and last data cell is in the current column, the range can then be selected.

The following macro will select the column of data if you start with the active cell at the column heading.


Sub SelectOneColumnData()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This routine will select a non-continuous column of data
' when active cell is located in the column heading.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim ac As Range
Dim lRow As Long
Dim lc As Range
Dim col As Integer
Dim cr As Range

Set ac = ActiveCell
col = ac.Column
lRow = ActiveSheet.Rows.Count
Set lc = Cells(lRow, col)

' Find the bottom of the range then re-set the last cell range
Set lc = lc.End(xlUp)
lRow = lc.Row

' Set the current range from the active cell to the last row
' in the column with data
Set cr = ac.Offset(1, 0).Resize(lc.Row - ac.Row, 1)
cr.Select
End Sub

Microsoft Query Incompatible with Mac OS X Lion [Updated]

Lion picThe other day I read an article on how to prepare my MacBook Pro for Lion, the new Mac OS X coming out this month, and discovered that any application that requires PowerPC is incompatible with Lion and will not work. See update at the end of this post.

So I followed the instructions to check all the installed programs on my MacBook Pro and was surprised to find that Microsoft Query will be incompatible. This will affect any Excel for Mac versions you might have on your computer — 2004, 2008, 2011.

Microsoft Excel for Mac and PowerPC

I would venture a guess that 99.4% of all Excel users have never used Microsoft Query, but I’m one who does and thought this might be significant to a select few. (Notice that Open XML for Excel and Charts uses PowerPC too.)

Microsoft Query allows you to pull data into an Excel spreadsheet from “behind the wall,” so-to-speak, of a database or ERP system. I made a career out of doing this very thing. Getting data that others could not.

In Microsoft 2010 (Windows) the new PowerPivot can replace Microsoft Query but in Excel for Mac there’s no replacement.

Excel for Mac users should beware, Microsoft Query is an incompatible app in the upcoming Lion upgrade for Mac OS X.

[UPDATE]

Since I wrote this post Microsoft updated their Office 2011 software. If you have Microsoft Office version 14.1.2 or later Microsoft Query has been updated to Intel, as you can see in the screen shot below, instead of PowerPC, and will now work on your Mac with OS X Lion.

MS Office Update for MS Query

Thanks to J. Monroe who pointed out the update by Microsoft in a comment.

International Short Date Formatting for the TEXT Function

I have an Excel workbook that is used in two different regions where the date format is entirely different, the US and the UK. At the top of a report worksheet I use a TEXT function to inform the user of the date range.

Here’s what a US user sees:

    From: 6/6/2011 to 6/10/2011

Here is the formula:

    =”From: ” & TEXT(MIN(ExtractData!A:A),”m/d/yyyy”) & ” to ” & TEXT(MAX(ExtractData!A:A),”m/d/yyyy”)

The Min and Max dates are in column A on a worksheet named ExtractData.

The problem is trying to automatically change the date format in the second argument of the TEXT Function – “m/d/yyyy” – which is a string argument. We can use VBA to accomplish this, but first a refresher on the TEXT function syntax.

TEXT Function Syntax and Argument

The Solution

The Application.International Property solves this problem with the xlMDY argument, which is TRUE if the date order is month-day-year, and FALSE if the date order is day-month-year. This property is put into the Workbook_Open event and modifies a defined name constant that’s used for the second argument of the TEXT function.

Here’s how it’s done.

Create a Defined Name Constant for the Date Format

I created a Defined Name Constant to store a Short Date format, and gave it the name sd_format.

In Excel 2007 and 2010, choose Formulas > Define Name to bring up the New Name dialog box where you type in sd_format in the Name box, and type =”m/d/yyyy” in the Refers to box. Remember the equals sign.

New Name dialog box

In Excel 2003, 2008, and 2011 choose Insert > Name > Define to bring up the Define Name dialog box. Type sd_format in the Names in workbook box, then type =”m/d/yyyy” in the Refers to box. Be sure to use the equals sign.

Substitute the Named Constant in the TEXT Function

The sd_format defined name can now be substituted for “m/d/yyy” in the second argument of the TEXT function. Here’s the new formula:

    =”From: ” & TEXT(MIN(ExtractData!A:A),sd_format) & ” to ” & TEXT(MAX(ExtractData!A:A),sd_format)

Since sd_format is already a text string, enclosed quotes are not needed.

Create a Workbook Open Routine

In the VBA Editor, I created a Workbook_Open subroutine, which looks at the computers international setting for the US, and if TRUE changes the sd_format value to “m/d/yyy”, and otherwise changes it to “d/m/yyyy” for the UK short date format.

Private Sub Workbook_Open()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This routing updates the regional date setting format for the
' defined name sd_format.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Application.International(xlMDY) = True Then
ThisWorkbook.Names("sd_format").Value = "m/d/yyyy"
Else
ThisWorkbook.Names("sd_format").Value = "d/m/yyyy"
End If
End Sub

How it All Works

Each time the workbook is opened the Workbook_Open routine executes the IF-THEN-ELSE statement, which simply looks to see if the computers region setting is month-day-year, then sets the defined name sd_format to “m/d/yyyy” which is a US format. If the computer’s region setting for the long date format is NOT month-day-year (and presumably day-month-year) then sd_format is set to “d/m/yyyy” for the UK.

Every TEXT function using sd_format for the second argument will then have the proper short date format for that computer.

Note: Obviously if the region settings are changed on the computer while the file is open the file will have to be closed and reopened, but this would most likely be a rare occurrence.

Select One Column of Data with VBA

Let’s assume you aren’t using an Excel Table, List Object, or List for your data table and you want to select the data in only one column, which means the Header row is excluded. This can be simple if there’s data in every row of the column, but what if that’s not the case?

Select Column Data in a List

And assume for the moment that the example above extends down several hundred, or even thousand rows. I would get tired of manually selecting the data in a column by using the Ctrl+Shift+down arrow keyboard shortcut.

So I’d probably write some VBA code to handle this pesky task and link it to a button on the worksheet. Below is the VBA code.

Sub SelectColumnData()
Dim ac As Range
Dim cr As Range
Dim col As Integer

Set ac = ActiveCell
Set cr = ac.CurrentRegion
col = ac.Column - cr.Column

If cr.Rows.Count > 1 Then
cr.Offset(1, col).Resize(cr.Rows.Count - 1, 1).Select
End If
End Sub

The active cell must be in the column you want to select when running this macro.

Select Column Data

To insert the button in Excel 2011, 2007 (Windows), I’m using a Form control by choosing Developer > Insert, selecting the Button icon, and drawing a button on the worksheet with the help of the Alt key to snap to cell borders. In Excel 2011 (Mac) choose Developer on the Ribbon then click the Button icon and draw a button on the worksheet. The alt/control key doesn’t work to snap to gridlines.

Excel Form Buttons

Then I rename the button and assign the macro.

Assign Macro to Form Control

I can also assign a shortcut to the Macro instead of using a button, but the only issue with that is remembering what the shortcut is when I need to use the macro.

The Table Address and Structured Data in Excel

The data in an Excel Table is nicely addressed. Meaning when you ask Excel for the address of the Table you get the data only and not the Header row or, if toggled, the Total Row.

Excel Table Data Address

The Table above starts in row 4 of the worksheet with a Header and ends in row 43 because the Total Row is toggled on. If I execute the following code inside the Immediate window of the VBA Editoronly the data range is returned.

? range("table1").address
$A$5:$L$42

The first line is the VBA code asking for the address of the Table1 range. The second line gives the requested range address.

When I select a cell inside the Table and ask for the range address of the current region, I get the data range, Header, and Total Row.

? selection.currentregion.address
$A$4:$L$43

If I toggle off the Total Row and run the same code the data and header is included in the current region.

? selection.currentregion.address
$A$4:$L$42

If you want to grab data only with VBA code and aren’t using a Table the following code will work. (In the Immediate window the code has to be on one line, so these two lines below are supposed to be one solid line of VBA code.)
? selection.currentregion.offset(1,0).resize(selection.currentregion.rows.count - 1,selection.currentregion.columns.count).address
$A$5:$L$42

This code selects the current region, offsets the entire range one row down, then resizes to one less row at the bottom.

Which is why an Excel Table is nicely addressed.

Excel Table Structured Data

Besides showing the column references in a Table, formula autocomplete shows us other structured data references.

Structured Table References

In the formula above, when I type an open square brace [ after the Table1 name, the formula autocomplete list appears and reveals that #All, #Data, #Headers, #Totals, and @ are available to be used as a reference to different parts of the Table.

The formula =ROWS(Table1) is the same as =ROWS(Table1[#Data]). And the Table1[#All] is equivalent to CurrentRegion.

Speeding up VBA with the PrintCommunication Property in Excel 2010

I have an Excel file that creates different reports from a data table using a macro, and before Excel 2010 was released the “time-hog” was running VBA code for the print settings. Tell me you’ve added something like the following code and had your execution time slow exponentially.
With ActiveSheet.PageSetup
.CenterHeader = "&""Verdana,Bold""&12&A"
.CenterFooter = "&P of &N"
.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.FitToPagesWide = 1
.FitToPagesTall = False
End With

Another factor is the default printer of your computer when the code runs. I usually set my default printer to CutePDF, which isn’t really a printer, but a program that writes (prints) to a PDF file. Using this default printer while running the PageSetup code chops mega-time off the execution.

One trick I used was to run this code in the Workbook_BeforePrint module. That way, running my macro to create a new report doesn’t execute the PageSetup code and therefore executes much, much, much, much, much faster.

The New PrintCommunication Property

In Excel 2010 I happen to record a macro for print settings and discovered a new page setup property:

Application.PrintCommunication

The Application.PrintCommunication property is new in Excel 2010. It specifies whether communication with the printer is turned on. The values are Boolean and Read/Write.

Using the PrintCommunication Property

Set the Application.PrintCommunication equal to FALSE, then run the Page Setup code, then set it back to TRUE and your code will run exponentially faster. However, there are two problems with using the Application.PrintCommunication property and both deal with compatibility.

Compatibility with Older Versions

Older versions of Excel don’t recognize this new property so you have to code around this fact to ensure backward compatibility. I checked the Excel 2010 version number by running some code in the VBA Editor’s Immediate window.

Excel Version code

You can also check your Excel version by choosing File > Help and reading the screen.

Since the Application.version property returns a String value, the Val function is required to make use of it. My code now reads like this to achieve backward compatibility:
If Val(Application.Version) >= 14 Then Application.PrintCommunication = False
With ActiveSheet.PageSetup
' (all the ugly page setup code goes here)
End With
If Val(Application.Version) >= 14 Then Application.PrintCommunication = False

I checked to see how long it takes to run the VBA code to produce my report in three versions of Excel: 2010, 2007 and 2003.

Excel 2010 – run1 = 0.918 sec, run2 = 0.867 sec
Excel 2007 – run1 = 5.965 sec, run2 = 6.273 sec
Excel 2003 – run1 = 6.066 sec, run2 = 6.125 sec

My default printer at the time was a HP D110 and it took over 5 seconds longer to run the PageSetup code in Excel 2007 and 2003.

Compatibility with Excel 2011 for Mac

By now I’m happy with Excel for adding this new property, but reality sets in and I realize that Excel 2011 can run code too. I check to see what version number Excel 2011 for Mac happens to be, and it too is version 14.0.

Excel 2011 version

So while I’m in the immediate window I check for the Application.PrintCommunication property and find out it doesn’t exist.

No Print Communications property

So I have to use the OperatingSystem property of the Application object to give me another condition.

Excel 2010 Operating System

Excel 2010 Operating System

Excel 2011 Operating System

Excel 2011 Operating System

I use the Left function to grab the first three letters of the operating system name.

Left(Application.OperatingSystem, 3) = “Win”

Then change my code to account for earlier Excel versions AND the Windows operating system as follows:

If Val(Application.Version) >= 14 And Left(Application.OperatingSystem, 3) = "Win" _
Then Application.PrintCommunication = False
' (my page setup code here)
If Val(Application.Version) >= 14 And Left(Application.OperatingSystem, 3) = "Win" _
Then Application.PrintCommunication = True

Using the PrintCommunication property of the Application Object will help speed up your code execution. However, you need to be aware of backward compatibility for earlier versions of Excel that don’t recognize the code, and with the Mac version of Excel 2011 that should have the same property, but doesn’t.

Automatically Expand a Named Range in Excel

I usually put a name to each data table created for referencing information in in Excel, as in a Named Range. If you add data to the bottom of the table, the Named Range isn’t modified and any reference to it will fail to include the new information.

Here’s a table of data I stuck on a worksheet called MyData and the range A2:E10 is named myFoodData.

Named Range to Expand

Inserting a row inside this range will automatically expand the reference for the Named Range, but normally a user would add data to bottom of the table in the first empty row.

My solution is event based. I write a simple subroutine.

Sub ShiftRangeAndRename()
Const n As String = "myFoodData"
Dim rng As Range
Set rng = Range(n).CurrentRegion
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count)
rng.Name = n
End Sub

Then reference it from the deactivate routine on the MyData worksheet.

Private Sub Worksheet_Deactivate()
Call ShiftRangeAndRename
End Sub

When a user goes to the MyData worksheet and updates data and returns to the main worksheet, the worksheet deactivate routine calls the routine to update the range reference and its associated name. This also works if they are deleting data, but that’s not common in this type of situation.

Calculate the Xth Weekday of Any Month in Excel

I’ve a simple formula for calculating the Xth Weekday of ANY month. It takes four inputs: Year, Month, Week, and Day. And requires a couple of lookup tables for data validation, one with special formatting.

Xth Weekday of a Month

The History

It all started when my wife mentioned there were a couple of meetings she had to schedule at work, one on the second Tuesday of every month and the other on the third Tuesday of every month. That got me to thinking about how to create a formula in Excel to calculate those dates.

After getting a partial solution, other things took precedence and it slipped from my mind until Chandoo posted a homework article on how to figure out what date Thanksgiving falls on each year, the 4th Thursday of November, in the USA.

His solution and all the variations provided in the comments (I posted one too) was focused and specific to one day (Thursday) of one week (the 4th) in one month (November). Albeit a link in that article showed another formula solution to Canada’s Thanksgiving day that takes place on the 2nd Monday of October.

This served to spur me on and finish what I started. I hope you like it.

The Inputs

Xth Weekday of the Month

Input for the Year in cell A2 is done manually. Just type in a year.

Input for Month in cell B2 is from a data validation drop-down list of values from 1-12. Merely done for convenience.

Input for Week in cell C2 is from a data validation drop-down list of values from 1-4. These are the only values that work correctly in the formula.

Input for Day in cell D2 is from a data validation drop-down list of values from 1-7 that use the “ddd” custom cell format to convert those numbers into an actual date. (You can’t actually type in a text value because what you’re seeing is a formatted date. Below I’ll show an alternate formula where you can use text values like Sun, Mon, … Sat.)

The Formula

The formula for Date Selected in cell E2 is uses the “ddd, m/d/yyyy” custom date format (US) and the formula is:

=DATE(A2,B2,(C2*7+1)-WEEKDAY(DATE(A2,B2,8-DAY(D2))))

Instead of putting all the calculations in the Day argument of the DATE function, you can also write the formula as DATE – WEEKDAY:

=DATE(A2,B2,(C2*7+1))-WEEKDAY(DATE(A2,B2,8-DAY(D2)))

The Explanation – Why This Works

WEEKDAY Inverse RelationshipThis formula has two parts, subtracting the WEEKDAY from the DATE. They both hinge on the fact that WEEKDAY function values are 1-7 for days Sun-Sat, and that an inverse relationship exists for the first week of the month, as shown by the chart. (Eight minus the WEEKDAY value)

Column three is the Day value, or 3rd argument, of the DATE function inside the WEEKDAY function. This relationship works for every day of the week. For a Tuesday, we have WEEKDAY(DATE(Year, Month, 5)). For a Thursday we have WEEKDAY(DATE(Year, Month, 3)).

The DATE Function relies on the Week number 1-4 to generate a date that is one day past that particular week. C2 * 7 +1, is just giving values 8, 15, 22, or 29.

So for a Thursday Thanksgiving in the USA you have

DATE(Year, 11, 29 – WEEKDAY(DATE(Year, 11, 3)))

and for a Tuesday Thanksgiving in Canada you have

DATE(Year, 10, 15 – WEEKDAY(DATE(Year, 10, 6)))

It helps to visualize this formula in the Evaluate Formula dialog box and step through the formula iteration.

Evaluate Formula Xth Weekday of a Month

The Data Validation

One critical aspect of this formula is to get a numerical date value from cell D2 for the DAY function. I use values 1-7 and convert them to Dates by custom formatting the cells with the “ddd” format.

Format Integers to Day Format

Of course this resulted in my date range being 1 Jan, 1900 to 7 Jan, 1900 and is okay for the Windows Date System.

Note: Excel for Mac users check to make sure you’re not using the 1904 Date System, in Excel>Preferences>Calculation look under the Workbook options to see if Use the 1904 date system is unchecked.

If you want to make this work with either date system (Windows or Mac) then choose a month where Sunday is the first day, like 1 Aug, 2010. You’ll need seven dates ending with 7 Aug, 2010.

Alternate Formula Without Custom Formatting

You can, of course, skip the custom formatting for the Day in cell D2 by using straight text values in the data validation list.

Data Validation Text Date List

This requires a different formula and below I’m utilizing the VLOOKUP function.

=DATE(A2,B2,(C2*7+1)-WEEKDAY(DATE(A2,B2,VLOOKUP(D2,{“Sun”,7;”Mon”,6;”Tue”,5;”Wed”,4;”Thu”,3;”Fri”,2;”Sat”,1},2,FALSE))))

The VLOOKUP reads cell D2 then finds the corresponding value in the constant array and returns a number.

Evaluate Xth Weekday of a Month Alternate

To shorten this up considerably, create a Named Constant, like MyWeekday, to replace the array.

Named Constant Array for MyWeekday

The formula can then be reduced to:

=DATE(A2,B2,(C2*7+1)-WEEKDAY(DATE(A2,B2,VLOOKUP(D2,MyWeekday,2,FALSE))))

I’m sure there are other variations. Do you have a better solution?

Download the Files

Here’s a file with the original formula that you can download.

XLSX File Format: Excel 2007, 2010, 2011

XLS File Format: Excel 2003, 2008

Here’s another file with the alternate formula using the VLOOKUP with constant array.

XLSX File Format: Excel 2007, 2010, 2011

XLS File Format: Excel 2003, 2008

Single-Cell Array Formula in Excel

A Single-cell Array formula is powerful, yet can be hard to understand. The Evaluate Formula dialog box helps by revealing array values Excel holds in internal memory.

In the spreadsheet you see pictured, a single-cell array formula for the Cumulative Plan is located in cell B2. It summarizes the historical and current day’s Plan data. The Cumulative Actual formula, in cell C2, summarizes the Actual data in the same manner.

As an introduction to Array formulas, in my last post I added two columns. The multi-cell array formulas for columns D and E are:

{=$A$2>=A5:A18}

{=D5:D18*B5:B18}

Extra Columns with multi-cell array formulas

Both formulas are entered by selecting their respective ranges, typing in the formula, without curly braces, and using Ctrl+Shift+Enter. Excel provides the curly braces, confirming it’s an array formula.

I didn’t include the current day in my last post, but decided to make a slight change in the column D formula here. (>= instead of >)

In the column D formula, the absolute reference to cell $A$2 could be replaced with the TODAY() function with the same result.

To summarize these two formulas: The Past compares Today’s date to the Date in column A and returns FALSE if the Date is in the future, and TRUE if not.

The MTD Plan multiplies this result times the Plan, and because TRUE = 1 and FALSE = 0, all rows with future date values become zero (0) and otherwise shows the value for the Plan.

A Temporary Single-Cell Array Formula

Summarizing the MTD Plan in a single cell with an array formula will give me a temporary solution for the Cum Plan. In cell B2 I enter the array formula:

{=SUM((D5:D18)*(B5:B18))}

Again, this is done by entering =SUM((D5:D18)*(B5:B18)) and pressing Ctrl+Shift+Enter. Excel provides curly braces.

This formula multiplies together two arrays, then summarizes with the SUM function.

With a single-cell array formula, the Evaluate Formula dialog box allows me to see how this formula is being process by Excel. On the Ribbon, select the Formulas tab, then click Evaluate Formula. In Excel 2003 it’s Tools → Formula Auditing → Evaluate Formula. Excel for Mac doesn’t have this feature.

Evaluate Formula Single cell array step 1

To see the result of the underlined expression, click the Evaluate button. You can see below that D5:D18 is an array that Excel is holding in internal memory. The curly braces are a dead give-away for an array.

Evaluate Formula Single cell array step 2

The next time the Evaluate button is clicked, B5:B18 shows up as a second array with numerical values.

Evaluate Formula Single cell array step 3

Recall from my last post that TRUE = 1 and FALSE = 0. Multiplying these two arrays together gives a single array, which you can see by clicking the Evaluate button again.

Evaluate Formula Single cell array step 4

The SUM function will now evaluate the array. Clicking Evaluate one more time will show the resulting answer, which is 2000.

My Single-Cell Array Formula

I now want to get rid of columns D and E so all references to them have to be replaced. To edit the single-cell array formula for Cum Plan, I select cell B2, click inside the Formula Bar, replace D5:D18 with $A$2>=A5:A18, then press Ctrl+Shift+Enter to get:

{=SUM(($A$2>=A5:A18)*(B5:B18))}

Since cell A2 contains the TODAY() Function, the following formula works as well:

{=SUM((TODAY()>=A5:A18)*(B5:B18))}

The formula’s calculation progression is similar to the previous screen-shots of the Evaluate Formula dialog box.

Single-Cell Array Evaluate Formula

The TODAY() Function evaluates to the date serial number 40490, which is November 8, 2010, and is compared to each cell in the array A5:A18 to get an array of TRUE and FALSE values. The second array B5:B18 is expanded to show the numerical values. These two arrays are multiplied together to get a single array of values, which the SUM Function then evaluates and returns the value 2000 to cell B2.

A single-cell array formula can also be applied to the Act Plan for cell C2, which is:

{=SUM((TODAY()>=A5:A18)*(C5:C18))}

And those are my single-cell array formulas.

Note on Excel for Mac

Apparently there is no Evaluate Formula option in Excel 2008 or 2011 for Mac. I have both programs and it simply doesn’t exist. One of the help forum answers suggested using the F9 key, when editing a formula, to show the values in an array, but that doesn’t work for me. back

Hide or Show Names in Excel with VBA

If you’ve discovered how useful Names can be in a spreadsheet, you may also know they can get-in-the-way-if-you-have-too-many. Just check out the Name Box in the picture to the right.

Each Name has a Visible property that’s set to TRUE by default. You can hide a Name by changing the Visible property to FALSE, but only in the VBA Editor.

Hide Names in Excel

If you have a great many names to hide, it’s best to loop through the Names collection and change the Visible property value to TRUE for all Names.

Sub HideAllNames()
Dim n As Name
For Each n In ThisWorkbook.Names
If n.Visible Then n.Visible = False
Next n
End Sub

After running this code, you now have a blank Name Box.
Excel Name List Blank

If you just want to hide a few Names, the code below, executed in the Immediate Window of the VBA Editor, will do the trick. Just change out “MachName” for each Name you want to hide.

Hide Name with VBA Code in Immediate Window

Go To Hidden Names

You can still Go To a Name that is hidden, just type a Name in the Name Box and hit enter.

Show Hidden Name with Name Box

Or use the keyboard shortcut Ctrl+G or F5 to bring up the Go To dialog box, type a Name in the Reference text box, and click OK.

Show Hidden Name with GoTo Dialog Box

If the Name’s corresponding range address is on a hidden worksheet, this won’t work.

Show Names in Excel

While working on a file, it can sometimes be advantageous to actually SEE ALL the Names. The following VBA code will do just that.

Sub ShowAllNames()
Dim n As Name
For Each n In ThisWorkbook.Names
If n.Visible = False Then n.Visible = True
Next n
End Sub

Keep Some Names Visible with Code

Sometimes you may want to leave a few of the Names visible for users. In the code below I’m looping through the Names collection, setting the Visible property to False, then checking to see if any have “Lookup” in the Name property string and making those Visible.

Sub ShowSomeNames()
Dim n As Name
For Each n In ThisWorkbook.Names
n.Visible = False
If InStr(1, n.Name, "Lookup", vbTextCompare) > 0 Then
n.Visible = True
End If
Next n
End Sub

I use the VBA Function InStr, which stands for In String, to check and see if the Name Property of each Name has “Lookup” contained in it, by starting at character 1. If the InStr Function finds “Lookup” with in the text, it returns a numerical value, which represents the character position of where “Lookup” starts within the text string. If the InStr function doesn’t find “Lookup” within the Name Property, it returns a zero (0).

In the code above, if the InStr function finds “Lookup” within the Name Property string, the code sets the Visible property to TRUE. Here’s what shows in the Name Box after running this code.

Excel Name List INSTR Lookup

You can open up the Excel VBA Editor, create a new module, copy any of the first two code snippets, paste into the module, and it should run just fine. The third snippet is more specialized and would need modification of the “Lookup” attribute for it to work.

My apologies for not indenting the code above, but I’m still learning HTML and CSS.

Related Posts Plugin for WordPress, Blogger...