Since we need to locate a conversion value based on two inputs, a 'from' time unit and a 'to' time unit, we need a two-way lookup formula. Entering a value as =1/7 is an easy way to avoid entering long decimal values. It’s where you can create a visual representation of your expenses and analyze them in detail. Expense Capture Dashboard (B) The Dashboard worksheet is the main user interface for this template. Note: Customize conversion values to meet your specific needs. You enter your daily expenses in this worksheet and then filter the list based on single or multiple months using the blue Slice buttons. To populate the table itself, we use a mix of simple formulas and constants: So, if we want to convert a monthly expense to an annual expense, we match the 'monthly' row, and the 'annual' column, and return 12. For the purposes of this example, we want to match the row first, then the column. The 'from' units are listed vertically, and the 'to' units are listed horizontally. The conversion table has the same values for both vertical and horizontal labels: daily, weekly, biweekly, monthly, and annual. Once you have the value, you can simply multiply by the original amount. This can be done, but you'll end up with a long and complicated formula.Ī cleaner approach is to build a lookup table that contains conversion factors for all possible conversions, then use a two-way INDEX and MATCH formula to retrieve the required value for a given conversion. You might first be tempted to consider a chain of nested IF formulas. The second sheet is a streamlined summary chart of your budget vs. The first sheet is dedicated to tracking your expenses in various categories throughout weeks, months or years. An example of the 1 month template for tracking expenses in Google Sheets- A simple.

Like so many challenges in Excel, much depends on how you approach the problem. Expense Tracking Sheet This template is perfect for both personal use, as well as small business use. Enter the dollar amounts that you paid for each expense in column B. For example, if we have a monthly expense of $30, we want to calculate an annual expense of $360, a weekly expense of $7.50, etc. Select an empty cell beneath the last item in your income column. The goal is to convert an expense in one time unit, to an equivalent expense in other time units. Open your income and expenses Excel worksheet. This is a daily and weekly personal expense tracker for printing out on paper. Named ranges: data (O5:S9), vunits (N5:N9), and hunits (O4:S4). This monthly personal expense tracker for Excel functions as a worksheet. This formula uses a lookup table with named ranges as shown below:

Daily expenses excel spreadsheet