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 … Read the rest
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 … Read the rest
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 … Read the rest
I 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 … Read the rest
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 … Read the rest
The 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.
I would venture a guess … Read the rest
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 … Read the rest
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?
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 … Read the rest
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.
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 Editor, only the data range is returned.
The first line is the VBA code asking for the address of the Table1… Read the rest
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.
.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
Another factor is the default printer of … Read the rest
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.
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 … Read the rest
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.
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 … Read the rest
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:
Both formulas are … Read the rest
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()… Read the rest
Dim n As Name