2016 Resource ‐ Excel Exporting & Formatting
4/4/2016
Exporting & Formatting Budgets from FlexGen, NextGen & Zortec into Excel
Objectives
• Discuss various options for opening and importing worksheets • Identify parts of the Excel window and worksheets • Explain basic Excel options for formatting • Review tools in Excel that might be beneficial for working with budget worksheets • Discuss preparation of worksheets to allow them to be imported into accounting software
Excel Program Window
1
2016 Resource ‐ Excel Exporting & Formatting
4/4/2016
Opening an existing workbook
Click Open. The Open dialog box will appear.
Opening FlexGen Budget Files
•
FlexGen budget files are tab delimited text files.
•
These will need to be opened using the Text Import Wizard in Excel to create columns correctly.
•
The file may be in a DiskCopy directory or a shared folder.
Opening your FlexGen budget file in Excel Open Excel first. Then use the Open option from the FILE menu to Browse to the location of your file. Selecting the file to open will bring up the Text Import Wizard.
If you do not see your file, make sure the file type is set to “All Files”, not “All Excel Files”.
2
2016 Resource ‐ Excel Exporting & Formatting
4/4/2016
Opening your FlexGen budget file in Excel Text Import Wizard Screen 1: Choose Delimited for FlexGen budget file. Click Next.
Opening your FlexGen budget file in Excel Text Import Wizard Screen 2: Choose Tab as the delimiter. You will see a preview of the columns at the bottom. Click Next.
Opening your FlexGen budget file in Excel Text import Wizard – Screen 3 You can change the format of the columns if desired. Otherwise, they will be set to General format. You can also select ‘Do not import column’ for any column selected. Click Finish.
3
2016 Resource ‐ Excel Exporting & Formatting
4/4/2016
Opening Zortec Budget Files •
Zortec budget files are CSV format (Comma Separated Values).
•
The file is often located in a DQEXP directory, but this is not always the case.
•
Filename is usually GLBUDGXP.CSV, but may vary. You could also have separate files for each department if that option was selected.
•
Just go to the location of the CSV file and double‐click the file to open. The text import wizard is not necessary.
Zortec Budget File File will open in Excel automatically with a column for each field that was chosen when building the file. File will not have headings in most columns. Numbers will be in General format.
Opening NextGen Budget Files:
•
NextGen budget files can be exported as Excel format (.xls) or CSV format (Comma Separated Values).
•
Either type of file should open automatically in Excel and will include all the columns from the Budget Worksheet in NextGen.
•
Just go to the location of the file and double‐ click the file to open.
4
2016 Resource ‐ Excel Exporting & Formatting
4/4/2016
NextGen Budget File File exported from NextGen will have headings for each column. Numbers are automatically formatted as currency. NextGen files have the account number in a single column (column A) AND in separate columns (usually the last columns in the worksheet).
Add Headings Select the first cell in the worksheet (A1). Use the Insert Sheet Rows option on the HOME Tab to Insert a Row at the top and then add your chosen headings.
Wrap Text If you have headings that you want to display on two lines. Type the full text into one cell, then select the Wrap Text option on the HOME Tab.
Before:
After:
5
2016 Resource ‐ Excel Exporting & Formatting
4/4/2016
Freeze Top Row The Freeze Panes option is on the VIEW tab. Freeze Top Row allows you to scroll through the worksheet while keeping the headings on top. Can also be used to Freeze first column.
Insert Columns or Rows Columns will insert to the left of the selected column.
Insert options are located on the Cells group of the HOME tab.
Rows will insert above the selected row. Cell formatting will copy from the column to the left of the new column or above the new row.
Insert Dialog Box
You can also insert columns and rows by right clicking on a cell and choosing Insert…
Hide Columns or Rows You can hide rows or columns on your worksheet to simplify your view. Select the column(s) or row(s) by clicking on the letter above the column or the number to the left of the row.
After the selection is highlighted, right‐click the column and choose Hide.
Hidden Column
6
2016 Resource ‐ Excel Exporting & Formatting
Unhide Columns or Rows
4/4/2016
Hidden Column
To show the column again, right‐click on the hidden column and choose Unhide.
You can also hide/unhide columns from the Format button in the ‘Cells’ section of the HOME tab.
Quick Number Format Options Easy number format options on the HOME tab. You can choose one of the preset options, or choose More Number Formats to customize. The first cell that is selected will preview in the list of quick format options.
Number, Currency and Accounting formats automatically show 2 decimal places, but can be adjusted with the buttons below the drop down box.
On the Ribbon bar, click on the corner of the Number Format group to show all the formatting options available.
Currency vs. Accounting: Currency Accounting Negative numbers will show in parentheses with Accounting format. With Currency, you can choose how they are shown.
Cell Styles Cell Styles allow you to apply multiple format options at once.
Cell Styles button on the HOME tab:
There are several preset options, or you can create your own. For example, the Bad/ Good/ Neutral options could be used to highlight budget trends. Use the Normal button to remove all formatting options in selected cells.
7
2016 Resource ‐ Excel Exporting & Formatting
4/4/2016
Format Painter Format Painter is another way to quickly format cells. You can select any cell on the worksheet, then click the Format Painter button
The format painter shortcut is on the Clipboard group of the HOME tab.
Before:
After:
Any cell(s) selected immediately after will take on all formatting of the selected cell.
Format as Table The Format as Table option can be used to quickly format a section of your worksheet into a preformatted Excel table with Filter options for each column. There are many built‐ in table formats available. Once you have created the table, you will see Table Tools options on the Ribbon bar.
Format as Table Creating a table from the data automatically adds filter options to each column. You can leave all the data in one table, but filter your view to see only data meeting the criteria.
Use the dropdown arrows to see the filter options for each column. Filter options for number fields:
Filter options for text fields:
Once you are finished reviewing the filtered data, you can remove the filter(s) to view all the data again.
8
2016 Resource ‐ Excel Exporting & Formatting
4/4/2016
Format as Table Example: We are filtering by the GLAccount Number column to see only fund 314 in our table. Since our column is formatted as General, we see text filters in our dropdown. We choose ‘Begins With’ and enter 314 to show only those accounts.
Calculating Prep Field Amounts You can calculate new amounts into prep fields with a formula. Formula bar
Formulas can be typed into the cell, or the formula bar. Formulas must start with the = sign. Formulas will always show in the formula bar when the cell is selected. Changes to the formula can be made there.
Example 1 : 5% increase This formula is adding 5% to the current year amended amount for Prep field 1.
Results:
Example 2 : 3% decrease This formula is subtracting 3% from the current year amended amount for Prep field 2.
Results:
Subtotals DATA Tab – Subtotal At each change in: Choose Function or Function Description Use function: Sum Add subtotal to: Choose each column you want to see totals by Function
9
2016 Resource ‐ Excel Exporting & Formatting
4/4/2016
Subtotals by Function Description (1)
(2)
Results are summarized but can be expanded with the on the left side. The can be used to change the view between the Grand Total (1), the Subtotals (2), and the detailed amounts (3).
(3)
Preview and Print Worksheet To print your worksheet, go the FILE tab and choose Print. You will see a preview of the print job on the right side. It will list the number of pages at the bottom of this screen. You can change any page layout options on the left side before printing. When ready to print, click the print button at the top.
Set Print Area
Set Print Area will allow you to print a certain portion of your worksheet easily when you might be making changes and reprinting the same section(s) repeatedly. A worksheet can have multiple print areas defined. Each one will print on a separate page or pages.
10
2016 Resource ‐ Excel Exporting & Formatting
4/4/2016
Preparing spreadsheet files to import into FlexGen Budget •
FlexGen can import a Tab Delimited (.txt) or Comma Delimited (.csv) type file.
•
Choose Save As from the FILE tab and choose the file type.
•
Account numbers must be in separate columns for each segment (Fund, Function, Object, etc.)
•
You can tell FlexGen which column contains the information needed, so they do not have to be in a certain order.
Preparing spreadsheet files to import into Zortec Budget •
Zortec can import only a Comma Delimited (.csv) type file.
•
Choose Save As from the FILE tab and choose the CSV file type.
•
Account numbers must be in a single column (ex: 110‐43100‐111).
•
At least one column must contain the amounts to be imported into a Prep field.
Concatenate Function to combine columns First add a new column
Account numbers must be in one column to import into Zortec Budgeting, but you may have exported them by segment for working in Excel. You can use the CONCATENATE function in Excel to join the segments into the correct format.
With the new column selected, click on the Function button next to the formula bar.
Type Combine or Concatenate in the search box then click Go. Choose the Concatenate function in the second window.
11
2016 Resource ‐ Excel Exporting & Formatting
4/4/2016
Concatenate Function to combine columns As you enter the cells, Excel will give you more fields to enter.
Text1:Click on the cell with the fund or type in the cell location. Text2: Type a dash – (Excel will add the “ “ for you) Text3:Click on the cell with the function or type in the cell location. Text4: Type a dash – Text5:Click on the cell with the object or type in the cell location.
Preparing spreadsheet files to import into NextGen Budget •
NextGen can import a Comma Delimited (.csv) type file or an Excel type file (.xls, .xlsx).
•
Choose Save As from the FILE tab and choose the file type.
•
Account numbers can be in a single column or in separate columns for each segment.
•
At least one column must contain the amounts to be imported into a Prep field.
12