Do you have data in Microsoft Excel that gets updated on a daily or weekly basis? Instead of copy-and-pasting the entire dataset over to Minitab every time or hunting to find where you left off last time, what if you could open Minitab and all of the new data was automatically imported?
Let's look at some ways you can bring data into Minitab from Excel that will allow you to setup a more permanent connection between the two. No more copy-and-paste!
To set up a connection between Minitab and Excel, we need to tell Minitab the file path (directories, folders, etc.) to where that Excel file lives. The simplest import of an Excel file is by using the File > Open Worksheet command in Minitab.
In the Open Worksheet dialog box, the first step is to click the “Files of Type” drop-down list and choose “All.” This lets us see all file types in the folder. Navigate to your Excel file and select it.
But before you click “Open,” take a look at the buttons that appear at the bottom of the dialog box after you select the Excel file. Click “Preview” to view how Minitab is recognizing the data in the worksheet. Then you can click “Options” to specify which data in the worksheet you want to import.
Since Excel is a general, cell-based spreadsheet, your document may have data in any row or column with formulas scattered in between. Minitab, as a statistical software package, requires the data to be in column-wise format (which is why it's easy to manipulate data with the Data menu in Minitab). Because of this difference, you want to avoid bringing over any header or footer information from Excel. Just focus on bringing over the raw dataset into Minitab. Use the Open Worksheet > Options box to specify exactly which rows to import.
In addition to using Open Worksheet for Excel files, you can treat Excel as a database and open the Excel file with Minitab's File > Query Database command.The advantage of this approach is the flexibility to filter on more elements of the dataset.
With Query Database, you can filter on exactly which columns of information you want to be imported into Minitab and even narrow down the dataset with if-then types of statements. Just click on the “Use Rows…” button in the Query Database dialog box. A menu that looks very similar to Minitab’s Calculator will appear and allow you to qualify the data before it is brought over to Minitab.
For example, if the data represents many years of information, you can narrow it down to just the data collected in the past year. Further narrow it down to all data collected in the past year and only from a specific facility or production line.
The key to using Minitab’s Query Database tool is that the data in the Excel file must be in a “database-style” or column-wise format. It cannot contain extra information beyond the raw data.
| Not in "Database" Format | Minitab-Ready Format | |
So what are all these extra steps gaining me? Where is the value? Right here: Once we show Minitab the file path using Open Worksheet or Query Database, we can save those commands to have Minitab call that data again and again.
Click on the Show History icon in the Minitab Toolbar. The History window displays all the commands run in Minitab since you opened the project. Highlight the Open Worksheet or ODBC command lines (including subcommands indented below the main command) and any graphs or analyses you do on a regular basis, such as Control Charts, and right-click to Save As. Save the file as an Exec File (.MTB) to your local computer or network drive.
Now all you have to do is double click that Minitab file to pull in the latest information from Excel. This method works great if you have recurring reports that have to be run every day, week, month or year.
For an example of how this can make one's working life more efficient, check out this case study where a quality team prevented the manufacture of out-of-spec product by monitoring automated Control Charts created in Minitab. For additional information or help on setting up a Minitab macro for your process, contact Minitab Technical Support.