How do I save an Excel file with a macro?

There may be times when you will want a macro to save a file automatically after it is run. The second macro will save the file with a name called "MySavedFile". You may specify the path if you need to. The last macro saves all opened workbooks.

Sub SaveFile()
ActiveWorkbook.Save
End Sub

Sub SaveName()
ActiveWorkbook.SaveAs Filename:="C:\MySavedFile.xls"
End Sub

Sub SaveAll()
myFile = ActiveWorkbook.Name
    ActiveWorkbook.Save
    ActiveWindow.ActivateNext
Do While mySavedFile <> ActiveWorkbook.Name
    ActiveWorkbook.Save
    ActiveWindow.ActivateNext
Loop
End Sub

Shared Apartment Expenses Spreadsheet

Have you ever been in that situation where you are sharing a living space with a number of other people and you are constantly trying to figure out who owes who what because each utility is in a different person's name? I've moved fourteen times in the past six years (because of going back-and-forth from university to co-op every semester) and I've had different roommates at each location. I needed a quick and easy way to keep track of who owed me money or what I owed someone else and to make sure all the bills were paid. I wanted this to stay updated in real time so I could simply look at it whenever and know exactly what was due and who to pay, all without needing to sit down do the math every time. This has lead to the creation of my Shared Apartment Expenses Spreadsheet. The following example is for an apartment with three roommates.

The first thing I do is list a description of what utility or rent is due. In the next column I list the amount due. The next column is the category where the expense falls under. These are actually drop-down lists which originate on another sheet where I listed out all of the possible categories. See data validation if you don't know how to make a drop down list. I only did this so I could look at statistics for each month and category.

I also put the due date for each bill and when it was actually paid for my records. Now the important part: who paid what? I make a column for each person and every time they pay a bill I put the amount under their name next to the item that they paid. 

The next section is who owes who what. Now this could be a simple formula if you have decided to split everything evenly among yourselves. But come on, life is never that simple. The apartment I am living in has a one car garage and a master bedroom. It's not really fair to the person that gets screwed out of the garage and doesn't even get their own bedroom! Therefore, we decided to split the rent like so: 

  Instead of having a simple formula you have to manually enter the amounts for the rent. Every other utility is split evenly. The totals are listed at the bottom of the columns. Now, the next thing we want to prevent is to have to write a million checks. If you owe me money, but I owe you more money, I simply subtract what you owe me then write you a check. 

Next is the all important who paid what. This is more complicated and confusing the more people you have but it works and in the end makes things much easier. I list each person and what they've paid to the other two people. I then add up the total at the bottom.

Alright, so that's the easy and obvious part but I want to know what the balance is right now. Below the who paid what section is where the magic happens. First, in cell R16 I take what Nick owes Brian and subtract what Nick PAID Brian (=K12-R12). In the next cell I take what Nick owes Ron and subtract what Nick PAID Ron (=L12-S12) and so on for the other two people. Now we can see who owes who what after payments. Brian owed Nick $86.71 but after a payment of $22.16 only owes him $64.55.

However, Nick also owes Brian $31.67, which is less than what Brian owes Nick. So, we subtract what Brian owes Nick so that Brian just has to pay Nick $32.88 instead of $64.55. Make sense? The formula looks like this: 


=IF(R16>T16,R16-T16,0)


If what Nick owes Brian is greater than what Brian owes Nick, subtract the two, otherwise put zero. Nick's balance for Brian is zero. Brian's balance is greater than Nick's so subtract the two and get $32.88. Basically, each person either has a zero balance or they owe money to someone. There should be no negative values.

The last line tells you who you owe money to in real time. Every time someone makes a bill payment or pays someone else it automatically updates and adjusts the values. No math involved! No more fighting over who paid what and when.

On the last sheet (Statistics) I decided to get a little crazy and keep track of some statistics: the totals for each month and which utility was the biggest drain on my wallet. I used this formula for the utilities

=IF(Statistics!B$1=Balance!$C4,Balance!$B4,"")


And this formula for the months: 


=SUMIF(Balance!D:D,A15,Balance!B:B)

This is also a good place to check the totals with the master sheet to ensure your work is correct.

I had to make some quick graphs to visualize the data. Who doesn't love a good pie chart? 

And there you have it, another practical, real world application of Microsoft Excel!

 

 

 

 

Excel Spreadsheet Macro: Highlight Duplicates

This is a simple yet effective macro for your Excel spreadsheet. There are times you need to highlight duplicate data in your worksheet. You could use the duplicates function but that actually ends up deleting everything that is a duplicate. Sometimes you may just want to point out what is a duplicate and not physically delete that data. This macro is what you could use instead.

Sub DupinRed()
Application.ScreenUpdating = False
Rng = Selection.Rows.Count
For i = Rng To 1 Step -1
myCheck = ActiveCell
ActiveCell.Offset(1, 0).Select
For j = 1 To i
If ActiveCell = myCheck Then
Selection.Font.Bold = True
Selection.Font.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Select
Next j
ActiveCell.Offset(-i, 0).Select
Next i
Application.ScreenUpdating = True
End Sub

How do I create a ratings system in Excel?

Normally, this blog is about me trying to help you with Excel spreadsheets. Well today, maybe you can help me. I am trying to come up with a new rating system in Excel. I don't want this to end up being a popularity contest, so I want to use experience as a criteria too. Users will rate an item A through F (could be anything from restaurants to roller coasters) with a value of 1 to 5, 1 being lowest and 5 being highest. Not every user will rank every item, only the ones they have ever experienced. To account for this I have a user experience index. I counted the total number of items experienced by each user, ranked them in reverse order, then divided by the total number of users (in this case there are 5). Thus, each users experience index is between 0 and 1, with 1 being the most experienced user.

I also wanted to incorporate a popularity index. I count how many users have used each item, rank them in reverse order, then divide by the total number of items. The popularity index is between 0 and 1, with 1 being the most popular item.

So now that I have this data I am not sure what to do with it. How can I combine them in a meaningful way? I've tried this formula:

(User rating * user experience index)/SUM(user experience index) * popularity index

The problem is probably with the popularity index. With it being linear, it gives far too much advantage to the most popular items, and goes too far to penalize those that don't see as much action. It over-compensates for the problem I described. I don't remember anything from the one statistics class I took in college so I'm not sure what to do. It feels like the linear experience index actually works really well, it just seems like the popularity index is off.

Any suggestions?

NCAA Bracket Madness by David Tyler

We have a special treat for you today. Our very first guest blogger is David Tyler, the creator of what I consider to be the best March Madness Excel bracket on the internet, as I stated in a previously. I really appreciate David taking the time and sharing his insight, experience, and wealth of information with us today. Now, on to the article!
 -------------------------------------------------------------------------------------------------------

Every March, thousands of people fill out NCAA basketball tournament brackets, for which someone is faced with the challenge of running the pool.  This process can be tedious and fraught with human error.  To alleviate this, we can use a two-file Excel solution: a Bracket file submitted by all pool participants, and a Pool Manager file used to track the results once the tournament has begun.

Nick asked me to provide a brief posting on how these files work.  Below is a basic overview, but I recommend poking around the files to learn more.  Please bear in mind that the bracket files aren't perfect, and there are better ways that I've often been too lazy to implement, but the current files serve their purpose.

The Brackets

Most importantly: we need good, clean data.  Let's say there is a game involving Massachusetts.  If the participant puts the winner as "UMass," a human understands what that means, but Excel doesn't.  The file needs to have "Massachusetts" exactly.

To keep data consistent, all participants must submit the exact same bracket file.  By using VBA code that allows the user to click on a cell to advance that team, there is no opportunity for mis-typing.  The user clicks on "Massachusetts" in cell B4 and it automatically advances the cell's contents ("Massachusetts") to cell C5.  The user sees this as a quick and easy way to fill out a bracket; in reality, its main purpose was to help ensure good data.

For users who don't/can't enable macros, the file also uses a combination of Data Validation, formulas, and Conditional Formatting to ensure clean data.  Having alternative checks is an important component to maintaining clean data.  Many thanks to my friend Tom Szarek for the clever design of these great VBA-free features.

Collecting the brackets

With everyone using the same bracket file, we always know what data is going to be in which cells - e.g., C5 will always have the winner of the upper-left region's 1-16 matchup.  This allows the Pool Manager file's VBA code to open a participant's bracket (previously saved off to the pool administrator's hard drive), store the picks in an array, and then write those picks at a row of data in the Pool Manager file.  This is automatically repeated for all participants, storing all pool participant data on the same worksheet.  At this point, the Bracket files are no longer of any use.

Evaluating the brackets

Using formulas, we compare the actual winners of the games (from the MasterBracket tab) with the participant's picks.  If 'actual winner' on MasterBracket = the 'pick' on the Picks tab, the participant earns the points from that game.

The participant score is computed instantly with formulas on the Leaders tab.  VBA then sorts the leaderboard in descending order so the highest scorer is on top.  The resorting could be done with some array formulas, similar to those used on the PartInfo tab, but VBA is easier and less resource intensive.

Enjoy March!

Any time you are doing the same task multiple times, you need to question how Excel and VBA can be used to reduce the workload.  With these files, pool administrators can handle a large number of participants with little additional effort.  Further, once the Pool Manager file has all data, the administrator can send the file to all pool participants -- this lets participants track scores on their own, generate scenarios, and see other pool participants' picks.  The less time spent on administration, the more time there is to enjoy the games and the taunting of friends in the pool.

----------------------------------------------------------------------------------------------------------

That was great David, very informative, especially the part about reducing the workload when working with repetitive tasks. I've found that to be very true in my engineering work experience. Thanks again for sharing with us. Don't forget to check out David's blog here.

We're always looking for innovative and unique features and spreadsheets utilizing Microsoft Excel so please don't hesitate to contact me with your ideas or if you are interested in guest blogging.  Thanks for reading.

How do I make horizontal rows into vertical rows in Excel Spreadsheet?

To make horizontal rows into vertical columns, highlight the cells you want to change, copy, right click cell you want to move to, select ‘paste special’, click ‘transpose’ box, hit ok. It's that simple!

Join our free email newsletter for more advanced Excel tips to make your life easier! Don't worry, we won't swamp your inbox and you can unsubscribe at any time. We'll even send you some useful Excel templates from time to time. Cheers!
 

What’s the best 2010 NCAA Excel Bracket?

March Madness is here! Are you looking for a great way to start an NCAA Basketball Tournament pool with your friends or coworkers? After downloading several different Excel spreadsheet brackets I think I have found the best one.

http://whenthewhistleblows.wordpress.com/2010/03/08/ncaa-basketball-march-madness-excel-brackets/

Through the use of several macros, the bracket is very easy to use and has a very clean appearance (much more so than most of the other Excel brackets that I downloaded). You can even create html pages to post on the web the current standings. Download it and see for yourself!

 

(Note: there are two files to download, the blank bracket to distribute to all of your friends plus the Pool Manager to keep track of all your scores. Instructions are included!)