Vacation? Running a marathon? Building your dream house warehouse? Keep Track of Your Financial Goal 10 Incredibly Useful Spreadsheet Templates to Organize Your Life 10 Incredibly Useful Spreadsheet Templates to Organize Your Life Is your life a hotbed of missed deadlines, forgotten purchases, and broken commitments? Sounds like you need to get organized. Read More It's a simple and effective way to track a single variable, and one you can share with your team. Visualize your data and make your spreadsheets easy to use with an Excel Dashboard. a simple spreadsheet format is not attractive enough to make your data accessible. A dashboard allows you to present your most important data in an easy-to-digest format. Read More Let your own progress inspire you to do more!
We'll be building on Excel 2013, but you should be able to follow previous versions with no problem. This tutorial will explain how to make a basic thermometer chart with a grand percentage and grand total of the stated goal. Next, we will see how to use the SUM and SUMIFS functions. 4 Excel search functions to search efficiently. Spreadsheets. 4 Excel search functions to search efficiently. In a giant Excel spreadsheet, CTRL + F will only take you so far. Be smart and let the formulas do the hard work. Search formulas save time and are easy to apply. Read More
Before we build our thermometer, we need to set our target. In this case, I'm raising funds from the MakeUseOf back office for our long-awaited team building trip to Andorra, but you can use your own financial goals and markers.
Open Excel 2013 and open a new spreadsheet. For this part of the tutorial, we will only use two columns:one for the months and one for the amount deposited. Mine looks like this:
You'll notice the target, total, and percentage Cells under our table. This is where we will create the formulas for our thermometer.
In cell B20 you will have to write. =sum(B5:B16) , using the equivalent for your table position. This formula gives us the total of the numbers in column B. Once you've established your current total, we can calculate your progress as a percentage using another very basic formula.
In cell B21 I'm going to write. =B20 / B19 and press enter. It will give you a decimal amount. We can easily change it to our desired format (percentage) by right-clicking on the cell in question and selecting Format Cells . This should immediately open the Format Cells context box on the Numbers tab. Change the category to percentage , and press OK . Your worksheet should now look similar to this:
Now we've taken care of the table, the total, and the percentage, and set up our formulas. Mini Excel Tutorial:Using Advanced Counting and Adding Features in Excel Mini Excel Tutorial:Using Advanced Counting and Adding Features in Excel Counting and adding formulas can seem mundane when compared to more advanced Excel formulas. But they can help you save a lot of time when you need to gather information about the data in your spreadsheet. Read More
Go to Insert> Column> 2D Column> Cluster Column . This will open an empty chart next to our table. Add Data to Chart How to Use an Excel PivotTable for Data Analysis How to Use an Excel PivotTable for Data Analysis The PivotTable is one of the most powerful tools in the Excel 2013 repertoire. big data analytics. Follow our step-by-step demo to learn all about it. Read more using Select data . Select the cell that contains the percentage of your total. For me, this is cell B21. Press OK to fill the graph, and you will get to this screen:
Now we can remove the graph. Right click on the chart title and delete. Do the same for the column title and the horizontal lines. Double-click the y-axis (percentages) to open the dialog box. From here you can change the chart's minimum and maximum limits to 0.0 and 1.0, respectively. While you're here, scroll down and select Numbers . Change the decimal places to 0.
Right click on the column and select Format Data Series . Adjust the Slit Width to 0. This will ensure that your column fills the chart area, rather than trying to hide in the corner. You can now shrink the table to a size more like a thermometer. Finally, go back to the Insert tab, select shapes , and find a good oval. Draw an oval and add it to the bottom of the thermometer chart, then resize the chart area. It should fit nicely around the bell of the thermometer, like this:
We did it! You can change your thermometer to red by right clicking and changing the fill color.
If you're tracking a large amount of money over an extended period of time, it can be helpful to look back on which days you've raised the most money. This can be especially useful for charity drives - you can break down what your team did differently on those occasions and link it to your next fundraising event!
First, we are going to modify our data table. Mini Excel Tutorial:Use Boolean Logic to process complex data. Mini Excel Tutorial:Use Boolean Logic to process complex data. Logical operators IF, NOT, AND and OR can help you get an Excel novice. to empower the user. We explain the basics of each feature and demonstrate how you can use them to get the best results. Read more . We need a more detailed table, including dates. We'll also include names, because tracking our donors is fun. I'm not going to detail the transformation from a small table to a large table, but you should end up with a table similar to the one shown below. I'll show you how to get this sorted table format in a moment.
Remember:you need separate columns for date and amount received. In this way, we can monitor each variable. We will also need to set a dynamic named range. Named ranges are useful for giving us the power of a set of cells without having to constantly update our formulas Need help with Excel formulas? 7 resources to check out Need help with Excel formulas? 7 Resources to Reference Excel is the gold standard of spreadsheets. If you must use Excel and yet become familiar with it, these resources will quickly introduce you to the basics and beyond. Read more . We can automatically ask our formula to take into account any additions to our table.
To make things easier for us later, I've turned our basic table into an official table. Do this by selecting the entire area of your table. Select the Insert tab, and you should immediately see Table . Select OK. You have made a lookup table, with headers. Headers will come in handy later!
Remember our Objective, total, and Percentage cells from before? Now we can link our table to our total. In your total cell, input =SUM(Table1[Amount]) . This formula asks the cell to total the Quantity column. the Percentage Information can still be found by dividing the total for the target , and it's still linked to our thermometer.
Select the contents of your Quantity column. For me that is do 26:c 38. Select the Formulas tab, and locate Name Manager . Click New . Note the Refers to cash register. If you had the Amount selected column, =Table1 [Quantity], it should appear. We need to add to this formula:
OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C),1)
Every time you add a value to the Amount column, your total will automatically increase.
Your formula should look very similar to this:
=Tabla1 [Cantidad], COMPENSACIÓN (Hoja1! $ C $ 1,0,0, COUNTA (Hoja1! $ C:$ C), 1)
Es posible que tengas que adaptar la letra de la columna; aqui esta do .
SUMIFS es una fórmula poderosa que nos permite correlacionar información de dos o más fuentes. Vamos a utilizar SUMIFS para averiguar cuántas donaciones recibimos en un período de 14 días, entre dos fechas específicas. Así es como se verá el producto final:
Ingrese su requerido fecha de inicio . Para mí, esta es la celda B10. En la celda B11, escriba =B10 + 14 . Excel insertará automáticamente la fecha para usted y la seguirá actualizando según la celda B10. Ajusta el periodo de tiempo alterando +14 al numero que necesites.
La celda B12 contendrá nuestra fórmula SUMIFS. En la celda, escriba:
=SUMIFS ($ C $ 26:$ C $ 95, $ A $ 26:$ A $ 95,”> =”&$ B $ 10, $ A $ 26:$ A $ 95,”<=”&$B$11)
Whoah - eso es un montón de números, ¿qué hacen? te mostrare!
La celda B12 ahora debe expresar el valor de las donaciones recibidas entre las fechas especificadas.
Hemos logrado algunas cosas bonitas hoy. Ha utilizado fórmulas básicas, ha editado el área y el eje del gráfico, ha creado tablas, ha aprendido acerca de los rangos dinámicos con nombre y ha probado el increíble poder de SUMIFS..
Aunque como dice mi amigo a menudo, SUMIFS es simplemente SUMPRODUCT de un hombre pobre; Pero esa es otra historia de Excel, para otro día..
¿Rastreando su causa caritativa? Esperemos que esto haya ayudado? ¿Qué fórmula o función de Excel le gustaría que MakeUseOf le explique? Let us know below!
