Function Problems

How to Create an Excel Calculator for Tours and Total Costs

Adam Davis Oct 21, 2026 869 views

Question details

The user needs to build an interactive spreadsheet calculator to estimate total costs for tours, optional activities, insurance, and fixed costs, complete with dependent filtering and a reset button.

How to Create an Excel Calculator for Tours, Activities, and Total Costs

Product: Spreadsheet
Device & OS: not provided
Scenario: Setting up a customizable estimating tool for travel planning or cost calculation, involving dynamic variables and macros.
Observed behavior: A fully functioning calculator is required to dynamically sum selected costs, filter options based on previous selections, and quickly clear inputs for new scenarios.

Before you start

Before building the calculator, organize your pricing data by creating a clear master list of all tours, optional activities, insurance tiers, and fixed costs in a dedicated reference sheet.

Solution 1Recommended

Set Up Basic Drop-down Lists and Cost Calculations

Create the foundational tables and use Data Validation along with lookup formulas to allow users to select tours and calculate basic costs.

The core of any cost calculator involves organizing reference data and allowing users to select items without typing manually. This minimizes errors and ensures the formulas work accurately.

Create a Reference Data Sheet

Open a new worksheet and create separate tables for Tours, Activities, Insurance, and Fixed Costs. Ensure each table has one column for the item name and one column for its cost.

Apply Data Validation

On your main Calculator sheet, select the cell where users will choose a tour. Navigate to the Data tab and click 'Data Validation'. Choose 'List' under the Allow criteria, and select the range containing your tour names from the reference sheet.

Fetch Corresponding Costs

In the cell adjacent to the tour selection, use a VLOOKUP or XLOOKUP formula (e.g., =VLOOKUP(B2, ReferenceSheet!A:B, 2, FALSE)) to automatically display the cost of the chosen tour.

Calculate the Total Cost

At the bottom of your calculator interface, use the =SUM() function to add up all the fetched costs from the tour, activities, insurance, and fixed cost cells.

Set Up Basic Drop-down Lists and Cost Calculations

Solution 2

Create Dependent Drop-down Lists for Activities

Filter the available optional activities based on the previously selected tour or country to streamline user choices.

Solution 3

Add a Macro Button to Clear Selections

Automate the process of resetting the calculator for a new estimating scenario using a simple recorded macro.

Create Powerful Calculators Using WPS Spreadsheet

WPS Spreadsheet provides robust, easy-to-use tools for advanced formulas, data validation, and VBA macros, allowing you to build professional cost calculators effortlessly.

1. Organize Reference Data: Open WPS Spreadsheet and input your tours, activities, and prices into clean, structured tables.
2. Set Up Drop-downs: Navigate to Data > Validation to quickly configure both single and dependent drop-down lists using the INDIRECT function.
3. Automate Calculations: Apply SUM, SUMIFS, and VLOOKUP functions to dynamically fetch prices and calculate the final total.
4. Insert a Reset Button: Access the Developer tools in WPS to record a macro that clears input cells, then attach it to a clickable shape.

100% compatible with Microsoft Excel formulas and .xlsx/.xlsm formats.Intuitive interface for quickly setting up data validation and dependent drop-downs.Built-in support for VBA macros to automate tasks like clearing selections.Free and lightweight alternative to Microsoft Excel for complex data tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Do I need to know VBA programming to build an Excel calculator?

No, VBA is only necessary if you want automated features like a one-click 'Clear' button. The core calculator, calculations, and dependent lists can be built entirely using built-in features like Data Validation and formulas (SUM, VLOOKUP, INDIRECT).

Can I select multiple activities in a single drop-down cell?

Native drop-down lists only allow one selection per cell. To allow users to pick multiple activities, the easiest method is to create multiple rows with the same drop-down settings. Alternatively, you would need complex VBA code to append multiple selections within a single cell.

Why is my dependent drop-down list showing an error?

This usually happens if the Named Range in your reference sheet does not exactly match the primary drop-down's output text. For example, if your primary drop-down has 'Europe Tour', your Named Range must be 'Europe_Tour' (Named Ranges cannot contain spaces). Ensure exact matches and replace spaces with underscores.

Excel & Data Problems