Track an Office Budget from SharePoint PDF Data in WPS Office Spreadsheets
Question details
The user needs to create an Excel-compatible budget tracker in WPS Office Spreadsheets from a SharePoint PDF containing general ledger codes and categories, then calculate remaining budget amounts and percentages by category.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a PDF budget from SharePoint with general ledger codes and budget categories and wants to record expenses such as hotels, flights, and transportation.
- Observed behavior
- The user wants to calculate the remaining budget amount and percentage remaining for each category after entering expenses.
Have the SharePoint PDF budget open or exported so you can identify the general ledger codes, categories, and allocation amounts. Keep the expense records with date, description, category, ledger code, and amount ready for entry.
Build a Budget Tracker with an Expense Table and SUMIFS Formulas
Use this method when you have a PDF budget from SharePoint and need a reusable tracker that calculates remaining amounts and percentages by category.
Copying the PDF data into WPS Office Spreadsheets keeps the workbook self-contained and avoids a fragile link to the original SharePoint file.
SUMIFS formulas total expenses by category, so you can update the expense table without rebuilding calculations.
Open WPS Office Spreadsheets, create a blank workbook, and copy or type the general ledger codes, categories, and budget amounts from the PDF into a sheet named Budget.
Insert a new sheet named Expenses, then create column headers: Date, Description, Category, Ledger Code, and Amount.
Type each expense such as hotels, flights, or transportation into the Expenses sheet, using the same category and ledger code names as the Budget sheet.
In the Budget sheet, select the first remaining balance cell and enter =BudgetAmount-SUMIFS(ExpenseAmount,ExpenseCategory,CurrentCategory), replacing the references with your actual cell and range references.
In the next cell, enter =TotalSpent/BudgetAmount and format it as a percentage using Home > Number Format > Percentage.
Select the remaining balance or percentage cells, click Home > Conditional Formatting, and create a rule to highlight values below or above your threshold.
Build a PDF-Based Office Budget Tracker in WPS Office Spreadsheets
Use WPS Office Spreadsheets to copy budget data from a SharePoint PDF, log travel and office expenses, and apply SUMIFS formulas to track remaining amounts and percentage used per category.
- 1. Open WPS Office Spreadsheets: Download and open WPS Office, then create a new blank spreadsheet for your budget tracker.
- 2. Copy budget data from the PDF: Open the SharePoint PDF, select the ledger codes, categories, and budget amounts, and paste them into a Budget sheet.
- 3. Log expenses by category: Create an Expenses table with Date, Description, Category, Ledger Code, and Amount, then enter each hotel, flight, or transportation record.
- 4. Add SUMIFS calculations: Use =BudgetAmount-SUMIFS(ExpenseAmount,ExpenseCategory,CurrentCategory) for remaining balance and =TotalSpent/BudgetAmount for percentage used.
Frequently Asked Questions
Can WPS Office Spreadsheets connect directly to a SharePoint PDF?
Directly linking to PDF data is not recommended. Open the PDF, copy the budget table, and paste it into a WPS Office Spreadsheets worksheet so formulas can reference the copied data reliably.
How do I total expenses by category in WPS Office Spreadsheets?
Use SUMIFS with the category column as the criteria range and the amount column as the sum range. For example: =SUMIFS(ExpenseAmount,ExpenseCategory,CurrentCategory).
How do I calculate the percentage of budget used?
Enter =TotalSpent/BudgetAmount in the Budget sheet. Then select the cell, go to Home > Number Format, and choose Percentage to display the result as a percentage.
How can I highlight categories that are over budget?
Select the remaining balance or percentage cells, click Home > Conditional Formatting, and add a rule such as values less than 0 or percentages above 100%.
Can I save this budget tracker as a Microsoft Excel file?
Yes. Click the Menu button in the top-left corner, choose Save As, and select Excel Workbook (*.xlsx) to save a file that can be opened in Microsoft Excel.
