Update Cell Format in Excel Spreadsheet File¶

Many of the Volgistics report formats allow you to create reports as Microsoft Excel spread sheet files. This help topic explains how cells in Microsoft Excel spread sheet files you create in Volgistics are formatted, and how to change cell formatting to suit your needs using Microsoft Excel.

Each of the cells in an Excel spread sheet has a specific cell "format." A cell's format affects the way the information in the cell is displayed, and whether or not you can perform calculations on the information in the cell. For example, cells formatted as Text or General cannot be included in calculations until their format is changed to Number in Excel.

How Volgistics Sets Cell Formatting¶

Volgistics provides a series of different report formats. Each of these different report formats are listed on the Reports page when you choose Reports from the menu. The way cells are formatted in Excel files you create from Volgistics depends on the report format you use to create the Excel file.

The Volgistics report format called Excel spread sheet is designed specifically for creating Excel files from your Volgistics database. When you create Excel files using the Excel spread sheet report, Volgistics automatically formats each of the cells for the kind of data they contain. For example, cells that contain numeric data are automatically formatted with the number format.

The Excel spread sheet report is a good tool for moving raw data from Volgistics into Excel when you want to perform additional processing or analysis outside of Volgistics.

All of the Volgistics report formats other than the Excel spread sheet report are designed primarily for producing printable PDF files, however most (those comprised of rows and columns) can also be used to produce Microsoft Excel spread sheet files by choosing Excel on the Include page when you run the report.

When you choose to produce Excel files using any report other than the Excel spread sheet report, all of the cells in the Excel file will initially have the General cell format. If you would like to perform calculations in Excel files you produce using these other report formats, use Microsoft Excel to set the desired cell formatting for the cells you would like to include in your calculations.

Save the Excel File to Your Own Computer¶

Before you can make changes to Excel files you create in Volgistics (including changes to cell formats) you must save a copy of the file from the Volgistics mailbox to your own computer. If you have not already done so, save a copy of the Excel file you created in Volgistics to your own computer like this:

1. Choose Mailbox from the menu.

2. On the In Box tab, right-click on the name of the report and then choose Save target as (or Save link as) from the pop-up menu.

   

3. Use the Save As window to save a copy of the file to your desktop or to a specific folder (remember where you save the file and the file name so you can find it later).

Once you save the file to your own computer, start Microsoft Excel and open the file:

1. In Excel, choose File from the menu and then select Open.
2. Using the Open window, locate the file you saved to your own computer.
3. Click the Open button once you've located and selected the Excel file.

Format Cells as Numbers¶

Change an Entire Column to the Number Format¶

If you want to perform calculations on the data in specific column, it's usually easier to change the cell formatting for the entire column (rather that changing each cell individually). To change cell formatting from text or general to number for an entire column:

1. Select the entire column by clicking on the column heading. Be sure to select only a single column as shown here (selecting more than one single column will lead to problems):

   

2. Select the Data menu and choose Text to Columns.

   

3. In the Convert Text to Columns Wizard Step 1 of 3 window, choose Delimited and then click the Next button.

   

4. In the Convert Text to Columns Wizard Step 2 of 3 window, click the Next button.

   

5. In the Convert Text to Columns Wizard Step 3 of 3 window, click the Finished button.

   

The numeric data in the selected column will now be converted to the number format (you should see any numeric values in the selected column shift from left alignment to right alignment, indicating that they have changed to the number format).

Change a Single Cell to the Number Format¶

You can also change the formatting on individual cells from text or general to number like this:

1. Double click on the cell.
2. Press the Enter key.

The numeric data in the cell should change to the number format.