Fundraising is a critical aspect of many organizations, be it for charities, schools, or even personal causes. One effective way to track and display your fundraising progress is by using a fundraising thermometer. While many online tools are available, creating one in Microsoft Excel allows you to customize it according to your needs and keep your data readily accessible. Here's a step-by-step guide on how to create a fundraising thermometer in Excel.
Before we begin, ensure you have a basic understanding of Excel, including inserting shapes, using formulas, and formatting cells. Let's dive right in!
Preparing Your Excel Sheet
First, open a new or existing Excel sheet where you want to create your thermometer. Clear any existing data from the first row, as this is where we'll start creating our thermometer.
Using the 'Home' tab, click on 'Format as Table' and choose a style that suits your needs. This will give your sheet a more professional look, and you can adjust it later to match your organization's branding.
Creating the Thermometer Shape
Next, we'll create the thermometer shape using Excel's 'Insert Shape' feature. Click on the 'Insert' tab, select 'Shapes', and choose the 'Rectangle' option to create the thermometer's body. You can adjust its size and color later.
To create the thermometer's pointer, select the 'Line' shape, then draw a diagonal line across your rectangle. You can adjust its length and angle later to match the rectangle's size and give it a more thermometer-like appearance.
Setting up the ThermometerScale
Now let's create the scale for your thermometer. In cells A2 to E2, type the labels for your fundraising tiers, like '0-100', '101-500', etc. In the cells below these labels (A3 to E3), input the corresponding fundraising goals for each tier.
Format these cells as numbers, and ensure 'Accounting' or 'Currency' format isn't applied. Widen columns C, D, and E to align the labels and numbers neatly. Adjust the font size and color to match your thermometer's design.
Adding Formulas to Track Progress
Now that we have our thermometer's design set up, let's make it dynamic by adding formulas to track fundraising progress. We'll use a simple 'IF' function to monitor the donations against each tier's goal.
In cell A5, enter the total amount raised so far. Then, in cells B5 to E5, use the 'IF' function to check if the fundraising total meets or exceeds each tier's goal. For cell B5, use the formula: `=IF(A5>=B$3,"Yes","No")`. Adjust the cell references for cells C5 to E5 accordingly.
Formatting the Progress Indicator
To display progress visually, format cells B5 to E5 as green or red text, depending on whether the threshold is met. Right-click on the cells, select 'Conditional Formatting', then choose 'Highlight Cells Rules', and set 'Format only cells that contain' to 'equal to'. Enter 'Yes' and choose the desired green cell fill or font color. Repeat for 'No' but choose a red color.
Increase the font size of these cells for better visibility. You can also add a text box next to the thermometer to display the total funds raised dynamically by referencing cell A5.
Adding Formulas to Update the Thermometer
Finally, to keep your thermometer updated, enter the following formulas in the corresponding cells containing the line shape for each tier:
- For the first tier (cell F5): `=IF(OR(B5="Yes"),0.33*stücke$1,0)`
- For the second tier (cell G5): `=IF(OR(C5="Yes"),0.33*stücke$1,0.33*stücke$1)`
- Repeat for the third and fourth tiers, adjusting the cell references for D5 and E5.
Adjust the numerical value (0.33 in this example) to match the width of your thermometer shape. The 'OR' function ensures that if any higher tier is met, it contributes fully to the thermometer's height.
There you have it! Your fundraising thermometer in Excel is now complete and ready to track your progress. Keep updating cell A5 with the total funds raised, and watch your thermometer grow as you ascend through your fundraising tiers.
This thermometer serves as a visual reminder of your team's hard work and the collective impact of donors' generosity. Update it regularly, and don't forget to share it on your website or social media platforms to engagement and inspire more contributions. Happy fundraising!