Often times, when you download a data table from somewhere it’s not in the format you need it to be. Tables are often in a summary format where you have months going down and years going across, or vice versa. It’s the end result of what you want a pivot table to look like, but you can’t easily turn that into a pivot table itself. Below, I’ll show you how to turn a summary table in Excel that looks like this:
Into this:
This format is much more Excel-friendly and one that you can easily convert into a pivot table.
Converting the table
The data I’m using is the same one that I used in an earlier post that went over transposing data. Transposing data, unfortunately, isn’t enough to make data workable if you want to convert it into a pivot table. You’ll want data to be in a tabular format so that there’s a header for the month, year, and value.
You could manually transpose one year at a time and copy the data one by one. But of course, that isn’t optimal at all. The good news is I’ve got a macro that can help you flip that data in one click. It will go through the painstaking process of reorganizing the data for you.
Here’s the code for the macro. You can just put it into a module (I’ll leave a template to download below if you aren’t comfortable doing this step yourself):
Sub flipdata()
Dim cl, nxtcl As Range
Dim lastcol, lastrow, firstcol, firstrow As Integer
'get total number of rows and columns in range
lastcol = Selection.End(xlToRight).Column
lastrow = Selection.End(xlDown).Row
'get first column and row
firstcol = Selection.Column
firstrow = Selection.Row
'assign output starting point
Set nxtcl = Cells(lastrow + 2, firstcol)
nxtcl = "Header 1"
nxtcl.Offset(0, 1) = "Header 2"
nxtcl.Offset(0, 2) = "Value"
Set nxtcl = nxtcl.Offset(1, 0)
'cycle through data
For yr = (firstrow + 1) To lastrow
For mth = (firstcol + 1) To lastcol
nxtcl = Cells(firstrow, mth)
nxtcl.Offset(0, 1) = Cells(yr, firstcol)
nxtcl.Offset(0, 2) = Cells(yr, mth)
Set nxtcl = nxtcl.Offset(1, 0)
Next mth
Next yr
End Sub
It will output the data a couple of rows below where your data ends. It’s important to select the entire range of data before running the macro since it will go through the range that you’ve selected, nothing else. And if there’s data below your selection, it will overwrite that.
After you’ve selected the data, then you run the macro. In my template, I’ve got a button that you can press that will do the job for you and then you’ll get something that looks like this:
Once in this format, you can easily create a pivot table:
If you’d like to download the file that contains the macro, it’s available here.
If you liked this post on how to convert a summary table in Excel into a pivot table, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
A countdown timer can help you track how much time there’s left to do a task or until a deadline comes due. Below, I’ll show you how you can make a countdown timer in Excel that can track days, hours, minutes, and seconds. In order to make it work, we’ll need to use some VBA code, but it won’t be much. And if all else fails, you can just download my free template at the end of the post and repurpose it for your needs.
Let’s get right into it and start with the first step:
Calculating the difference in days,
To calculate the difference between two dates is easy, as all you’re doing is subtracting the current date and time from when you’re counting down to.
The start date is just going to be today, right this very second. And Excel has a convenient function just for that, called NOW. It doesn’t require any arguments and all you need to do is enter the following formula:
=NOW()
Entering the date and time you’re counting down to is a bit trickier. As long as you enter it correctly, then calculating the differences will be a breeze. However, this may involve a little bit of trial and error since it’ll depend on how your regional settings are setup. For the countdown date, I’m going to set it to the end of the year. Let’s say 11:00 PM on New Year’s Eve. Here’s how I input that into my spreadsheet:
2020-12-11 11:00 PM
The key things to remember here are that there should be a space between the time and the AM/PM indicator (if you use it) and there should be two spaces between the date and the time. Then, it’s just a matter of whether you’ve got the right order of date, month, and year. This is where you may need to do some testing on your end to ensure you’ve got the correct order.
Now that the dates are set up, we can calculate the difference in days. To do this, we can just calculate the difference and use the ROUNDDOWN function to ensure we aren’t adding partial days:
There are 222 days left until the end of the year. By using the NOW function, the formula will automatically update and tomorrow the days remaining will change to 221, and so on. If your output’s looking a little different, make sure to check the formatting and that it’s set to days.
Calculating the difference in hours, minutes, and seconds
There’s not a whole lot of complexity when it comes to calculating the difference in hours, minutes, or seconds. We’re still subtracting the current date from the deadline. The only difference is that now we’re just going to change the formatting. If I do a simple subtraction, I end up with a fraction, which isn’t really usable in its current format:
The trick here is to change the format of this cell so that it shows me hours, minutes, and seconds. And that’s an easy fix. If I just click on cell C10 and click CTRL+1, this will get me to the Format Cells menu. In here, I’ll want to select a Custom format so that the cells just shows hours, minutes ,and seconds:
Here’s what the countdown timer looks like after the format changes:
It’s important to include a date in the calculation even though we’re just doing a difference between hours, minutes, and seconds. Otherwise, the formula wouldn’t correctly calculate in all situations, such as when the deadline hour is earlier than our current hour.
Putting it all together
Now that all the calculations are entered in, now it’s just a matter of formatting the data. We can create a countdown clock that separates days remaining, from hours, minutes, and seconds remaining.
One cell can have the difference in days, while another will have the difference in hours, minutes, and seconds. This goes back to just modifying the formatting and applying a custom format. Here’s how mine looks:
Although we’ve gotten to this point, the challenge is that this countdown timer still doesn’t update on its own. Unless you want to click on the delete button all the time, the countdown isn’t going to move unless there’s something to trigger a calculation in Excel. That’s why we’re going to need to add a macro to help us do that, which bring us to the important last step of this process:
Adding a macro to refresh every second
We need a macro to update the file. Whether it’s every second, every five seconds, it’s up to you. While the countdown timer will update when someone enters data or does something in Excel, that’s not much of a countdown. This is where VBA can help us. If you’re not familiar with VBA, don’t worry, you can just follow the steps below and copy the code.
To get into VBA, click on ALT+F11. From the menu. Once you’re there, click on the Insert button on the menu and select Module:
Over to the right, you’ll see some blank space where you can enter in some code. Copy and paste the following there:
Sub RunTimer()
If Range("C10") <> 0 Then
Interval = Now + TimeValue("00:00:01")
Application.Calculate
Application.OnTime Interval, "RunTimer"
End If
End Sub
One thing you may to change is the reference I made to cell C10. Change that to where you have your countdown timer. As long as there’s a value in the cell, the macro will continue running. All it does is check if there’s a value there, and if there is, it updates the worksheet every second. And by doing that calculation, your countdown timer will update even if you’re not making any changes to the spreadsheet.
You can also change the interval which currently updates every second, as noted by the 00:00:01. You can change this to five seconds, 10 seconds, however often you want it to update.
But there still needs to be something that triggers the macro to start running. You can assign a button or shortcut key to do that.
However, in this example I’ll activate it when the sheet is selected. Inside VBA, you should see a list of worksheets. Double-click on the one that contains your countdown timer:
You’ll again see blank space to the right where you can enter code. And you’ll also see a couple of drop-downs near the top that you’ll want to look for. By default, the first one should say (General). Change this to Worksheet:
Next, change the other drop-down which will probably say SelectionChange. Change it to Activate. Then you should see something like this:
Copy the following code into there to call the macro we created above:
RunTimer
Now when you switch to another worksheet and come back to the current one you’ll notice your countdown timer is updating on its own. If you want it to stop it, just clear the cell that has the timer. Otherwise, the macro will continue running every second.
The Countdown Timer Template
If you’d rather just use a template, then you can download one that I’ve made here. You don’t have to worry about macros and instead you just need to enter the end time; the time that you’re counting down towards.
I’ve also got a start/stop button that you can toggle to get the countdown timer going and that will pause it:
You can move the button as well as the time your counting down to onto another sheet if you don’t want someone altering it. If you have any questions or comments about this template, please send me an email at [email protected]
If you liked this post on how to make a countdown timer in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
QuickBooks does a good job when it comes to recording sales and doing day-to-day accounting tasks. You may be content with the reports that come out of QuickBooks, too. But if you’re looking for some more in-depth analysis to do of your own or to make your own reports, you’re likely going to want to move that data into Excel. And, unfortunately, the QuickBooks export into Excel can be less than optimal.
With many spaces, subtotals, and a non-tabular format, it’s not a very practical output to use in Excel. If you want to run a pivot table and do some serious analysis in Excel, you first have to clean up the data before being able to use it, and that can be a very tedious and tiresome process.
For example, this is what your QuickBooks report might look like when you’re pulling a simple summary of your customer sales:
There are a lot of things that need to be adjusted for this report to be useable in Excel, including getting rid of the blank spaces and ensuring that the customer information is repeated in the first column, as opposed to just in the first line and in the last line’s total. From afar, it’s a bit of a painful process to have to go in and clean this up. And while it’s not impossible, it’s not going to be quick, either.
That’s where a macro can help you make the task much quicker and it will save you a lot of time if you have to go through these steps often. Click the button on the ribbon and your data will convert into a more table-friendly format! Here’s how it works:
Using the macro to fix the QuickBooks export
Before running the macro, you’ll need to specify the columns where your customer names and dates are:
Then, run the Covert Data button:
Downloading the file
The free version of the QuickBooks macro will allow you to run the conversion if it doesn’t go past 100 rows. However, if you decide to purchase the full version please ensure that the macro and file works as expected. There’s no guarantee the QuickBooks export hasn’t changed or won’t change in the future. If there are changes that need to be made to the macro, please feel free to contact me so that I can make the necessary adjustments. Whether you prefer the add-In or the actual Excel spreadsheet itself, both versions are available both here and in the paid version as well.
Here is the download link for the add-in as well as the Excel file. For the paid versions, please visit the product page.
If you have another program or software that you’d like a similar add-in for, I can help with that as well.
If you liked this post, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
If you want to customize the ribbon in Excel then you know simply changing doing it through the front end is only going to work on your computer. The customization isn’t technically saved within the file and it won’t move from one file to another unless you actually adjust the xml.
It’s by no means an easy process, but if you’re just looking to add a custom tab with some buttons for some macros that you have, I can show you a quick way to do that as painlessly as possible. It’s by no means comprehensive, but it’ll get the job done.
Step 1: Open your Excel file in a program like Winzip/7-Zip
If you right-click on your Excel file you should have an option that says Open Archive if you have a program like Winzip or 7-Zip installed. There, you should see something that looks like this:
These are the files and folders that are within the Excel file itself.
Step 2: Create a folder called customUI
Step 3: Open up notepad and paste the following into it:
Step 6: Close the archive, save changes and open the file
You should now see the two buttons at the end of the home tab:
The problem is that they don’t do anything just yet. In the code that you copied back in Step 3, there were some callback items (‘CB’) that we need to reference back to inside VBA. Those are effectively a link from when a user presses the button on the ribbon to the macro that you’ve coded.
What you’ll need to do now is go into a module within VBA and enter the following code:
Sub cbMacro1(control As IRibbonControl) ***name of your macro*** End Sub
Sub cbMacro2(control As IRibbonControl) ***name of your macro*** End Sub
In the subprocedures for the two buttons all you need to do is call your macro (in place of the code within the ***), and now the buttons should work.
Modifying the code to add more customization
In Step 3, the code there was for a couple of buttons that you could add to your ribbon. If you want to add more, simply following the sequence you can easily add another button:
The button id doesn’t matter too much itself. The label is what will show up underneath the button. ImageMso is the image that will show up. If you do a google search for ImageMso you’ll find what some of the different codes are.
You can shrink the image down to “small” by changing the size attribute here. Note that some of the ImageMso’s are already small and can’t be made large, but you can shrink large ones down in size.
The most important item when adding a button is the onAction attribute as this is what your callback code needs to reference inside VBA. Obviously the more consistently you name your buttons the easier it’ll be to add more without getting lost in your code.
Adding a new tab
In the above code, I added the buttons to the Home tab. However, if you’ve got more that you want to add then you can put them on an entirely new tab instead.
What you can do then is just change the following:
<tab idMso="TabHome">
into this:
<tab id="MyMacros" label="My Macros">
And now all of your macros will be saved onto a new tab rather than take up space on the Home tab. You can also group your buttons based on the group code as well.
This is a small sample of what you can do to customize the ribbon in Excel. The benefits of making these changes through XML and not within Excel’s interface is that your modifications are stored within the file regardless of what computer you open it on and won’t be lost.
There is another way that you can do this using the CustomUI editor and I’ll include that in a later post.
If you liked this post on How to Customize the Ribbon in Excel Using XML, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
If you’re looking to hire someone and want to know whether they know how to code in Excel using Visual Basic (VBA), it’s not too hard of a task to quickly evaluate whether they know it or not. The first step is to ask them to send you a sample of something that they’ve done that includes coding and that is not password protected.
Then you’ll want to test to see what it does. So you’ll want to ask how it works so you can see for yourself. If that code works and the macro does what it’s supposed to do, you might be thinking that will be enough. However, someone could simply use a macro recorder to try and generate the code. This is not the same as coding and anyone can do this with no knowledge of code whatsoever. But there’s an easy way to uncover this.
Finding the code
In the file that someone’s sent you, hit ALT+F11. This will send you into Excel’s backend and open up VBA. You should see something like this on the left-hand side:
Double click on each of those items – sheets, workbook, and any modules. Code can reside in any and all of those areas so you might need to cycle through to see where it is.
Once you find the code, that’s when you can start evaluating it.
Reviewing the code
Below, I’ll show you the same macro, how it might look in VBA compared to how it looks using the macro recorder:
coding using VBA
using the macro recorder
There are three things that should be clear from comparing the two examples above, which will help to identify whether someone’s just using the macro recorder or whether they’re actually coding properly using VBA.
1. Organization and spacing.
The macro recorder doesn’t care for spacing out the code and each line of code will come after the other. Especially when you’re looking at longer lines of code, it’ll get real messy real quick. Organization is important because if it looks like one big block of text it’s going to make it very difficult to audit or review later should you want to make changes.
2. No comments.
In the first example, there were lines in green that started with an apostrophe, called comments. They are optional but it ties back to the organization and putting notes along the way to help remind you what you were trying to do. It doesn’t have to accompany every line, but if you don’t see any comments at all, it could be a hint that someone just used a recorder. For a quick macro that’s only a couple lines long it probably wouldn’t be necessary, but for a lot of code you would certainly expect to see at least some comments.
3..Select.
In the second example, you’ll notice .select showing up multiples times. This makes it obvious that someone’s used the macro recorder. If you want to insert a column or bold it, you can just code it right away, you wouldn’t need to actually select it and then make the change. The macro recorder, however, records everything, including those selections. So seeing this should tell you right away that someone’s just used a recorder rather than coding it themselves.
There are other ways you could see whether the macro recorder was used or not but these three should suffice in helping you identify whether someone knows how to code or not.
Why does this matter?
If someone knows how to use the macro recorder, that’s good, but it’s not knowing how to code. The problem is that the macro recorder could do a small fraction of what is possible through actual coding. Coding through VBA opens up a lot more opportunities for automation and improving a spreadsheet. A macro recorder can be used by anyone but it lacks the sophistication to build much logic into it.
Excel can be a useful tool for tracking items, whether related to a meeting or a project. The action items template allows you to enter multiple fields related to an action item, including responsible person, department, expected completion, and tags to help organize them.
You’ll be able to create new action items, sort them into different department tabs, recall the ones you want when it’s a new meeting, update the items, and archive them when they’re done.
Let’s start from the beginning.
First, fill out all the fields relating to the action items from your meeting.
The tags field will help when you recall meeting items in case you only want ones related to sales, a project, or some other criteria. Tags can be separated any way you want – comma, space, or any other separator.
Then, click Save New Items, which will put them into every relevant tab.
By default, I have the sales and marketing tabs setup, but if you need more departments simply copy those tabs. If a tab doesn’t exist for the department, then you’ll get an error and it won’t be able to populate those tabs.
However, you don’t need to have a department for each action item and can simply assign a generic one.
The sales tab now shows the action item:
If a comment is left blank, then it will simply say ‘no update’ was made. In the comment field, it will always show the date of the meeting.
Now, say you want to make a new meeting and want to populate the action items. Click on the New Meeting button. This will give you the opportunity to enter any tags:
You can enter up to three different tags in your criteria. This is where if you have a specific type of meeting you can use the tags to help identify which items you want to populate in your meeting list. Of course, this assumes you entered the tag in the action item to begin with. If you do want to include everything, leave the tags blank and just click Done
When you pull up a new meeting it will only recall the most recent comment. Any items that show the completion at 100% will not populate the meeting items.
Any comments you enter now in the Latest Update field will add to the existing comments.
If you have items that are completed and don’t want to see them on the individual department tabs, you can click on the Archive Items button and that will move the items into the Archive tab.
This add-in is completely free and includes over 20 macros that I have worked on myself and that I hope will help you. Any feedback is welcome, as well as any suggestions for other macros you would like to see added.
Disclaimer These macros have not been tested exhaustively so I don’t offer any guarantees that they will work under every possible scenario. However, if you run into any issues please let me know and I will work to correct them. When using macros you should always save your work before executing them, as there is no undo button if something doesn’t go as expected.
If you understand and accept these risks, please feel free to download the file here
Below is an overview of all the different macros in the file.
Toggling Workbook and Worksheet Calculations
For those that work on large spreadsheets, this can make it easy for you to not only turn off and on calculations for a workbook, but for individual worksheets. It will also allow you to see whether or not they are set to on or off.
One of the things people sometimes don’t realize is if you turn off calculations in Excel at the workbook level, that disables it for all other workbooks. The danger is if you switch to another file you’re working on you may not realize calculations are still off, by seeing the toggle and whether it is set to on or off can help prevent that.
If you only need an individual worksheet to be off, you can do that as well. However, note that if the workbook calculations are set to off, then all the worksheet calculations will be off as well, regardless of whether or not they say on or off. Workbook settings will supersede any worksheet settings.
Very Hidden Tabs
Hiding tabs in Excel may seem pointless since even an average user would know that you can right-click and select un-hide. However, not many know that you can set them to be ‘very hidden’ and where right-clicking won’t do anything.
I’ve covered this in a post before here, and in this add-in I’ve made it so that you can easily both hide and unhide very hidden tabs.
Removing Excess Spaces
This macro will delete any trailing, leading, or extra spaces in a cell and will help to clean up your data.
Converting Formulas to Values
In some instances you may want to get rid of your formulas and replace them with their results (values), this macro will do that for you. Just select the cells and click the button and the formulas will be gone.
Converting Numbers to/from Text and Changing Signs
These buttons will allow you to choose whether you want to convert numbers that are stored as text into numbers, switch numbers back into text, or just flip the signs from positive to negative or vice versa.
Filtering Out Zero Values From Tables
If you’ve got a table or pivot table that has a lot of zero values in it that you don’t want to see, this will filter them out. This won’t get rid of errors, just zeros, and for it to work in a table, it assumes that the table will start in column A, otherwise it won’t filter the right column for you. The zero values will be removed from the column where your active cell is, so you have to make sure you’ve got the right cell selected before clicking this button.
Multiplying and Dividing by a Factor of 1,000
This is pretty straightforward and is mainly here since dividing can be helpful if you’re dealing with financials and want to cut down the number of placeholders. Multiplying will simply undo those changes.
Combining Columns
If you have data across multiple columns and want to combine it, you can do that with this macro. You don’t have to select entire columns, it can just be a selection. The columns don’t even have to be right next to one another.
Cycling Through Errors
If you want to find all the errors on the worksheet you’re on, this macro will cycle through all of them for you. You can correct an error, and click the Next button to go onto the next error in the sheet.
Removing Merged Cells
When you’re trying to do data analysis, merged cells can be a nightmare, and this will unmerge the cells and put the value into each of the cells as well.
Protecting Your Data
This will help convert your sensitive data into a random number preceded by a series of X’s. There’s a post here detailing how that process works.
Filtering Pivot Tables
If you’ve got a pivot table and want to select multiple items, it can be a tedious process. This macro will allow you to select what selections you want to filter by and apply them for you. But the first cell in the selection needs to match the field name in the pivot table.
Adjusting the Default Pivot Table Format
One of the more annoying things in Excel is that when you create a pivot table, it defaults to a format that isn’t very useful. This is what the macro will help you do:
Quickly Formatting Pivot Table Fields
If you’ve ever needed to change how fields are formatted in a pivot table you know that simply selecting the column and changing the format is a temporary fix. You need to actually go into the field settings. This macro will do that for you, and will set the settings to either comma or accounting format.
Quickly Extracting Unique Values
There are plenty of ways you can get unique values, but I thought an even easier way would be to select the cells and specify where you want those unique values to be output.
Counting Unique Values
If you just want to quickly count how many unique values are in your selection, this macro will do that for you.
Do a Reverse Lookup
Everyone knows how to do a VLOOKUP, but doing the reverse is another story. Take for example a credit card statement. You could have a lot of detail in the string, but only a certain few characters relate to the actual vendor or detail you want:
Using this lookup function the cells you select will be compared against a list you have specified, and if there are any matches, the corresponding field will be returned:
The result:
If you’re doing this with a lot of cells and have a big list, it could be time consuming, and that’s why I added a progress bar to this macro.
Comparing Sheets
This macro essentially looks at two sheets and tells you what is different, and will highlight the differences in them.
Updating Links
If you want to update the link for a cell, it’s not an easy process and involves you right-clicking on the cell and putting the link in there. This macro will do that step, and for the link it will put the cell’s value there, so if you put in the url you want in the cell and then run the macro on those cells, the links will be updated.
Adding the Location to the Footer
Clicking this button will add the path to the workbook you’re working on into the footer so when you print it out it’s easy to see where the file is saved.
With the stocks markets tanking earlier this month, I thought it’d be interesting to track their historical performance and put into perspective just how badly things have been going lately. For those that don’t know, one of my side jobs is writing articles for the Motley Fool Canada and so naturally this example attracted my interest.
However, there’s not an easy way to calculate this in Excel, and so I decided to go the route of a custom function.
What I’m going to be looking to accomplish is a way to to track how many consecutive trading days that a stock has been up or down, and then also calculate the cumulative value of those gains and losses.
If you’d like to follow along with my example, you can download the file I used here (you’ll have to save the file, open it in Excel and enable content, otherwise you’ll see NAME? errors)
Setting Up the Variables
I want the calculation to start from the bottom (the current cell) and work its way back up, since the latest results will be at the bottom. To do this I create a ‘bottom’ variable that looks like this:
————————————————————————————————————–
bottom = selection.Count + selection.Row – 1
————————————————————————————————————–
I want the user to be able to select what range they want the calculation to apply to, rather than selecting everything.
I also setup a variable for the column, which I named as offsetnum:
————————————————————————————————————–
offsetnum = selection.Column
————————————————————————————————————–
These two variables allow me to set my starting point for my calculation.
Determining if I’m Counting Negatives or Positives
The value of the starting cell will determine if I am going to be looking for positive numbers (gains) or negatives (losses), and so I setup an if statement to determine whether the first value is a gain or loss:
————————————————————————————————————–
If Cells(bottom, offsetnum) < 0 Then posneg = “negative” Else posneg = “positive” End If
————————————————————————————————————–
Start counting
The final step involves counting the values depending on whether I’m looking for positives or negatives:
————————————————————————————————————–
For counter = bottom To 1 Step -1
If posneg = “negative” Then
If Cells(counter, offsetnum) < 0 Then streak = streak – 1 Else Exit For End If
Else
If Cells(counter, offsetnum) >= 0 Then streak = streak + 1 Else Exit For End If
End If
Next counter
————————————————————————————————————–
My complete function looks as follows:
————————————————————————————————————–
Function streak(selection As Range)
Application.Volatile Application.Calculate
Dim bottom, offsetnum As Integer Dim posneg As String
‘Determine first value If Cells(bottom, offsetnum) < 0 Then posneg = “negative” Else posneg = “positive” End If
For counter = bottom To 1 Step -1
If posneg = “negative” Then
If Cells(counter, offsetnum) < 0 Then streak = streak – 1 Else Exit For End If
Else
If Cells(counter, offsetnum) >= 0 Then streak = streak + 1 Else Exit For End If
End If
Next counter
End Function
————————————————————————————————————–
Calculating consecutive points gains and losses
Now that I have a function to tell me the current winning or losing streak, I can calculate the cumulative gains and losses.
To do this, I am going to sum as far as the streak goes. And so far starters, I’m going to start with the SUM function. I am also going to use the OFFSET function because I need to determine how many rows up I need to add. The OFFSET will start from the current position and determine how far back I need to go to add up the totals in the current streak.
However, because some streaks are negative, I’ll need to also use the ABS function to just grab the number, regardless of if it is positive or negative. My formula looks like this so far:
=SUM(OFFSET(H2,1-ABS(I2), 0
Column H is where my gain or loss value is, while column I is the streak value. Since I want to sum the cumulative gains, I need to reference column H as my starting point.
I added the 1- before the ABS function because that will ensure the number is a negative, meaning that my formula will calculate upward, rather than downward if the number were positive. I also have to decrease the number of cells to offset because I don’t want to include the current cell, otherwise the formula will go too far.
Since I’m not offsetting any columns I set the next argument to 0.
The last argument I need to enter is the height of the offset function, otherwise the formula will just offset by the number specified in the second argument and pull that value, rather than pulling all the values that fall within the range.
This actually involves just copying the same argument again, but this time for the height. My completed formula looks as follows:
=SUM(OFFSET(H2,1-ABS(I2),0,ABS(I2)))
Note
Sometimes with custom functions you might notice that your calculations hang or stop computing correctly. What that means is you just need to recalculate using either F9 or you can edit in the cell and click enter, which will normally trigger a recalculation as well.
Alternatively what you could do is after running the formulas for the dataset, copy them over as values to ensure that they don’t change, since in this case you likely wouldn’t need to recalculate the streak value again.
Excel has a lot of different charts that you can use to summarize your data with. If you want to use your chart in PowerPoint or Word it’s an easy copy and paste job, but suppose you wanted to save the chart as a .gif, .png, or .jpeg file? Then you would need the help of VBA to accomplish that.
The code below will save the chart that you’ve selected as a .jpeg file into same folder as your Excel file:
Dim Fname As String If ActiveChart Is Nothing Then Exit Sub Fname = ThisWorkbook.Path & “” & ActiveChart.Name & “.jpeg“ ActiveChart.Export Filename:=Fname, FilterName:=”jpeg“ End Sub
The code above will save the file as a jpeg, but you can change it to .png, .gif, or whatever format you prefer by just changing the values in red.
Note: If your chart is small then your image will be as well. If you want the chart to save as a large image you’ll want to stretch it out first and then run the macro.
Once you’ve saved the code then what you’ll probably want to do is assign a shortcut key for the macro so that you can easily save whichever chart you’ve selected. You can read this post on how to insert code into VBA. It will also show you how you can assign a shortcut key to a macro.
When you’re creating a template in Excel for other users often times there is information in the backend that you’d prefer users not be able to access or modify. In some cases it might be preferable to just hide the data entirely, especially if it contains sensitive information.
The easiest way to hide a tab in Excel is simply to right click on a tab and click hide.
The problem with this approach is as easy as it is to hide you can unhide it as well, which many users know how to do, and just involves right-clicking on a tab and clicking Unhide
After that you can see all the sheets that are hidden and by select the sheet and clicking OK you can unhide it.
You could protect your spreadsheet and prevent users from changing the structure but that might not be preferable either as it involves password protection and will not allow users to insert, copy, or even rename worksheets. If they don’t need this functionality then the solution might work for you.
To protect your workbook simply select the Review tab and click on the Protect Workbook button and you’ll see a pop up where you can protect the structure. Make sure to just tick off Structure and enter a password and click OK.
Another way to hide tabs is through one line of VBA code. If I wanted to hide Sheet1 I could use the following code:
Worksheets(“Sheet1”).Visible = xlVeryHidden
If your sheet is named something else then you would just change Sheet1 to the name of the sheet you want to hide.
Now when I go back to my spreadsheet and right click unhide:
Unhide isn’t even an option because there are no tabs that can be unhidden. If I hid another tab without using VBA then only that one would be visible, but the one using the code I used above would not show.
If you want to unhide the tab, the code to unhide is as follows:
Worksheets(“Sheet1”).Visible = True
What you could do is have two different procedures, one to hide a tab and another to unhide it. Then you can assign a shortcut key to each procedure. This will easily allow you to hide and unhide any tabs that you want to be invisible.
If you are not familiar with VBA check out one of my first posts on how to insert code and assign shortcut keys to a macro.
