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 Read the rest

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 Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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.… Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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 … Read the rest

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:… Read the rest

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 Read the rest