Doing more with Excel in your business

14 Replies

I know that Excel is fantastic for crunching financial numbers for our RE deals, but it can do so much more.  I am in the process of building a spreadsheet that utilizes formulas, buttons, macros, and hidden sheets that contain my lease agreement, lease extension, work orders, all tenant letters, and more to simplify some of my important business processes. 

Only one sheet is displayed at a time and buttons are utilized to navigate through the system, keeping it clean and smooth.  When a new tenant has been selected, I open up this program, click on "Create New Lease" and fill out the form.  The information from the form displays in the hidden sheets based on the formulas built and I then use the print buttons to generate the document I need.  This process is the same each time and very quick to complete, while allowing for customization when needed.  

How are you using Excel to assist you with your business?   

Honestly, I think that looking for great deals is a better use of my time than creating Excel buttons.  But, to each his own.

@Bryan L.  

I appreciate your response and understand your position.  This program is not about "buttons" and pretty items.  It is about running your business systems in a consistent manner for the Landlord who is managing the tenants within the property.  Therefore, it really doesn't deal with finding any deals, just keeps the process of managing the tenants and paperwork an easier process.  And I agree with you, to each his/her own.

@Troy Bevans  I think a lot of it is about personality.  In my previous engineering career, there would always be one or two guys in the group who just loved sitting at their desk all day making excel spreadsheets.  I'm talking about all the bells and whistles.  I never had time for that myself.  But I imagine that they probably found their work to be more efficient after they spent the time to make all of that stuff.  It just wasn't for me.

Excel is like an appendage to my being...I don't know what I would do without it.  That said, I have not felt compelled to delve into macros, but this is pretty darn slick and has certainly garnered my interest.  

Nice work.

@Troy Bevans  I would imagine that having something like that with everything right at your fingertips saves a lot of time, and keeps everything in order, all of which gives helps free up time for deals. 

Now in my case, it would take me far longer to try to learn how one went about doing that than it could possibly be worth to me, but if it were something I needed and it was a program you could buy, I'd snap it up! 

@Troy Bevans  maybe you can post your example from your initial post in the BP fileplace with a link back in this thread so we can check out that example. Sounds like mail merge being used in a different and maybe more advanced way. 

@Bryan L.  

I think I know those guys...ha ha ha!!!  I am not concerned with bells and whistles.  I concern myself with the ease of performance and the efficiency of the system.  The phrase "just point and click" makes it simple to create the same result each and every time, which is one of the main goals for this.

Developing systems with excel or anything else all really comes down to is "PAIN". 

If the pain of doing something over and over and over again is to much then I'll put the work in to create a system that streamlines the process.  Thus getting rid of my pain. 

Granted if you know nothing about excel/development then you are sure your PAIN point will be greater with developing an excel spreadsheet then doing pretty much anything. 

Excel is so much more powerful then what 99% of people use it for.  The macros run with VBA (Visual Basic for Applications).  That is a Full programming language.  I can do some crazy **** with Excel.  I can connect it to a database, create input forms, have a login and even have it auto run processes to generate reports using a reporting tool.

For me it's almost always worth the effort of developing the "one click" solution.  What takes me a week to do will save me hours a week forever.  So worth it

The second post reminded me of my early career in computer programming. After obtaining my computer science degree, I landed an internship at a dot com. Online retailing was in its infancy and many issues had yet to be resolved with selling products online and dealing with inventory. One particular issue was customers who came to the website, selected items to buy, then for whatever reason, left the site without closing the sale. At the time, this would be tantamount to going into your grocery store, loading up a shopping cart, then just leaving it in the aisle and walking out the store. The next customer to come into the store wants a particular product, but the shelf is empty. Unfortunately, there was still one in inventory, it was just sitting in the shopping cart that was abandoned down on isle 5.

I developed software to deal with these abandoned carts and it had some other functionality that we don't need to get into. My software worked. It actually worked very well. However, I came into the office one morning, to my bare and boring cubicle devoid of any anime, action figures, posters, toys, and all the other trinkets techie guys are notorious for collecting and displaying, and sat down at my machine. I launched my software to see how it performed the night before (we also did a lot of IP tracking so we could find out who had visited and that way inside sales could do follow up calls) only to discover the interface had been completely redone! It was all purdy and cute. Whereas my original program just spit out text. Just the data and nothing but the data please! It was at that point I realized I had no business in computer programming. Yes, I could program, but I didn't love it. I didn't live for it. I didn't spend hours at end talking about things that happened in World of Warcraft (have personally never even seen it), unlike my coworkers who were all obsessed with online gaming, and computer programming. "Just working" wasn't acceptable to them. It had to have buttons, graphics and color.

I use Xcell a lot in my business. My databases, rent and utility tracking spreadsheets (easier to analyze than Quickbooks IMO), expected future expense budgets, even my rental application was constructed on an Xcell spreadsheet. I'm pretty sure I mentioned in my podcast for BP how my last business partner spent thousands of hours creating this amazing GUI (graphical user interface) in Access. He was constantly tweaking and changing it on me which was extremely frustrating for me. Now, I just use a simple Word doc to track leads - and it works perfectly. The point of my story is that yes, buttons, graphics and colors are cool, but like @Bryan L. mentioned, I also think it is purely a personality thing. I'd rather spend my time sipping ice tea on the back porch while watching the grass grow. Or buying real estate. Just depends on what time of the day it is.

Aaron - you no longer need GPS directions from your office to see properties, either. One less bit of technology. 

I just drove up to Avila beach and back and never used the radio in the new car. Prefer to listen to the engine not watch screen map.

Originally posted by @Rick Harmon:

Prefer to listen to the engine not watch screen map.

 Sometimes, getting lost is a great way to discover new things!

@Troy Bevans   I am a programmer in my other life, and I have created all kinds of Excel utilities. Some were adapted from projects I did for others, some purpose built from scratch. Things like a query tool that pulls records out based on various criteria, an automated mail merge utility that doesn't use word, a reformatting utility, etc. 

And I am with you, the time saved and consistency gained is very important. Systems make it all possible for me, which it wouldn't be otherwise.

I just do the basic analysis with Excel.  I think anything else is overkill

After reviewing some comments about macros, I thought I would share the only macros I actually utilize in my Excel program.  The macros used in this program are only to open and close a worksheet or print a hidden worksheet when pressing a button so that it keeps the interface clean...that's it.  As far as putting a button on the spreadsheet, no programming skills are required.    

Below is all it took for the macro to work, and I just keep reusing the two codes for each button I add and rename as required.

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

(MACRO #1 OPENS AND CLOSES WORKSHEETS)

Sub Open_MainMenu()

ActiveWindow.SelectedSheets.Visible = False

Sheets("Main Menu").Visible = True

Sheets("Main Menu").Select

Range("B20").Select

End Sub

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

(MACRO #2 PRINTS A HIDDEN WORKSHEET THEN HIDES IT AGAIN)

Sub Print_Lease_Agreement()

Sheets("Lease Agreement").Visible = True

Sheets("Lease Agreement").Select

ActiveWindow.SelectedSheets.PrintOut Copies:=1

ActiveWindow.SelectedSheets.Visible = False

End Sub

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

Once the macros are created, I insert a button and assign the macro to it.  When I click on the button, the assigned macro runs. 

Once you make one of these macros, just copy and paste to recreate it for another button to control a worksheet.  In fact, copy my code and try it out!  I am considering the creation of a 3 minute video to show how this is done in case anyone is interested.  I am a visual person and this is a whole lot of typing for me!!

Join the Largest Real Estate Investing Community

Basic membership is free, forever.

By signing up, you indicate that you agree to the BiggerPockets Terms & Conditions.