Save Data in Excel and CalPlanning

Saving in Excel vs Saving in CalPlanning

  • When you are connected to the server, you can pull data from CalPlan or HCP into Excel and you can push data from Excel to CalPlan or HCP

  • You can save CalPlan or HCP form data in Excel, make changes offline (not connected to the server), and upload them (Submit Data) to CalPlan or HCP at a later time. This is a common workflow for groups during the budgeting process.

Refresh vs Submit Data

  • Refresh pulls CalPlan or HCP data into Excel
    • Refresh overwrites any changes you have made in Excel and not submitted to CalPlanning

Caution: Each month following the close, Actual data from BFS is loaded into the Working version. If there are actuals for a combination of Account, DeptID, Fund, and Chart1 that did not previously exist in the plan, a new row will be added to the form when you Refresh.

  • Submit pushes data from Excel into CalPlan or HCP
    • You must be connected to the server to Submit Data
    • Be sure you are ready to submit data; there is no Undo
    • Recommendation: make a backup copy of your form before Submitting Data

Save Data in Excel

  • Use File / Save As to save your data in an Excel file.
  • If you are using Citrix, be sure to navigate through the folder structure to save to your drive. After selecting File / Save As, select This PC and follow this path - Windows (C:) \ Users \ YourName as shown below. Note: if you have Home (H:) in the left panel, this is a handy shortcut; it saves to the same folder. The Accessing Your Files When Using Smart View with Citrix job aid has step-by-step instructions with screenshots for this procedure.

Save As dialog box with Citrix

  • When you are connected to the server, you can use Refresh and Submit to pull and push data between your Excel file and CalPlan or HCP
  • To work with your data in Excel and not make updates on the CalPlanning server, open the Excel file without connecting to Smart View. If you are already connected, disconnect from the server by clicking the drop-down list next to the home button on the Smart View task list and selecting Disconnect All

Saving Your Workbook ≠ Submit

  • Unlike when using web forms, [Ctrl+S] only saves your Excel workbooks to your local desktop or server
    • [Ctrl+S] does not submit your data to the CalPlan or HCP database
    • If you are not using formulas, you can Submit then close your workbook without saving. As an example, you may have a formula in each month of your operating budget that multiplies the value in each month of your forecast by 1.03 to create a 3% year over year increase.
  • To send data to the CalPlan or HCP database, you must click the Submit Data icon in the Smart View or Planning ribbons
    • Submitting data from any tab on a composite (multi-sheet) form will submit all unsaved data on all tabs
    • To send data from multiple active worksheets, select the worksheets [Ctrl+click] or [Shift+click] and then Submit Data

Caution: Once you submit data to the CalPlan or HCP database, the Undo function will not reverse your changes.

Refresh Loses Unsubmitted Data

If you click the Refresh icon, you will lose any data that has not been submitted to the database. This is because Refresh sends data from CalPlan or HCP to Excel

  • Just like the Go button on the web form
  • You will be prompted before it refreshes

Actions for Saving and Submitting Data

Action When to Use What It Does What It Doesn't Do
[Ctrl+S] Only use if you have formulas, cell references or supplemental data in your workbook; if you do save your workbook locally, always use it to make further changes to your plan/forecast Saves a version of your form on your computer Does not send data to the CalPlan or HCP database
Refresh If you have made a mistake that has not yet been submitted to CalPlan or HCP and want to start over Refreshes the data on your form with the current data from the database for the POV selected Does not save or submit any changes you made (except formulas are retained)
Submit Data Often – while you don’t need to submit data every time you make a change, submitting frequently ensures you don’t lose your work Sends data to the CalPlan or HCP database Does not save data locally in Excel; cannot Undo after you Submit Data
Open Form Only as a last resort Re-opens your form with current data from the CalPlan or HCP database for the POV selected Does not save or submit any changes you make (including formulas)

Disconnect When Finished

To free up resources on the server, be sure to disconnect when you are finished using Smart View:

  1. Click the Panel button from the Smart View ribbon to display connections
  2. Click the drop-down arrow next to Home
  3. Select Disconnect All

Smart View panel with home menu open and Disconnect All highlighted

  Practice

Complete Exercise 5 to practice before continuing.