# Microsoft Excel Tips



## Repairman615 (Jan 10, 2011)

Hello,

I started using Excel for invoices after I learned I needed them. :blink:

I am a frequent user at an Excel forum using the same handle. Turns out a great way to learn Excel is to help others.

I am no guru - that is for sure. Anyone who says they are at this extremely vast program either is or doesn't really know what it can do.

I learn new stuff all the time about it and realize I don't know that much.

Thought maybe I could help out my fellow contractors.

Any rate, if anybody has a question, I would like to help. Excel is powerful and most likely has helped many of us.

Might not be able to solve all problems, but hey, we will give it a shot. :thumbsup:


-Jeff


----------



## 480sparky (Feb 1, 2009)

Can a cell be set to capitalize any letters it contains?

in other words, if I type in AbCdEf or abdcef, I want it to display ABCDEF.


----------



## PatsPainting (Sep 22, 2010)

From a quick google search


```
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Application.Intersect(Target, _
Range("$A$1:$G$15")) Is Nothing Then
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
End If
Application.EnableEvents = True
End Sub
```
1. Copy it

2. Right click your sheet's sheet tab then select "View Code" from the
popup menu

3. Paste the code into the code module that appears

4. Press Alt + F11 to get back to the usual Excel interface

just change the range to what ever cells you want and that's it.

Pat


----------



## Repairman615 (Jan 10, 2011)

Hello 480

The cell itself cannot change itself without VBA, however in another cell you could use the PROPER() function.

Like:
In cell say B1
=PROPER(A1)

Where A1 has the text to be upper case.

Pat has an example of VBA doing the trick through a worksheet change event.

-Jeff


----------



## griz (Nov 26, 2009)

Hit the caps lock on the m, double tap f2, shift on the semi colon & hit the 8 key...:whistling

You tech guys really crack me up...:thumbup:

Just don't have a clue what you're talking about

Seriously my hat is off to you guys:thumbup::thumbup:


----------



## Repairman615 (Jan 10, 2011)

griz said:


> Hit the caps lock on the m, double tap f2, shift on the semi colon & hit the 8 key...:whistling
> 
> You tech guys really crack me up...:thumbup:
> 
> ...


:laughing:
If you would have kept a strait face I was gonna try it!

Funny!
:laughing:


----------



## WildWill (Jun 6, 2008)

I'm sure you guys already know this but I'll throw it out anyways. Most Excel sheets will import right into Google Docs so that you can set up access groups, modify and have access to you docs anywhere, etc... I use them for my partner and I.


----------



## Repairman615 (Jan 10, 2011)

Hey WildWill,

I have not tried that personally. 

I did read a thread about some loss of functionality doing so. 
Have you found that to be true?


-Jeff


----------



## WildWill (Jun 6, 2008)

I could totally see that happening but with all the ones that I have done everything has transferred right over. In many ways I feel like I should just use Google docs instead of first doing things in Excel but I am used to doing it this way.


----------



## WildWill (Jun 6, 2008)

Like I said mine are really simple, here is a link to one that you can tinker with if you like.

https://docs.google.com/spreadsheet/ccc?key=0AuQ2aX6fBQhQdFNUYlhKblNUdk1ScDItalZEWERHNGc&hl=en_US


----------



## Repairman615 (Jan 10, 2011)

I was actually checking out the GoogleDocs and looking at a blank spreadsheet. It seems there are most functions, including the IFERROR(), which I have found handy.

Thanks for the link, I will have a play with it.

-Jeff


----------



## WildWill (Jun 6, 2008)

Enjoy, that was neat seeing ya on there.


----------



## Repairman615 (Jan 10, 2011)

Wow, that has alot of potential.

I am a one man show so sharing files isn't a real issue.

Still that was interesting, thanks

Jeff


----------



## insink71 (Sep 3, 2011)

WildWill said:


> Like I said mine are really simple, here is a link to one that you can tinker with if you like.
> 
> https://docs.google.com/spreadsheet/ccc?key=0AuQ2aX6fBQhQdFNUYlhKblNUdk1ScDItalZEWERHNGc&hl=en_US


Sweet. I love Google docs. I am an android phone guy and they sync up nicely with the phone. Also, making a cheap tablet model (on another thread http://www.contractortalk.com/f17/android-tablet-go-project-management-103972/).


----------



## Magic Hammer (Dec 11, 2007)

How about sharing some your best spreads sheets with us? I'm always looking for better forms.
Thanks!
Are you close to Nashville? I used to live there.


----------



## Repairman615 (Jan 10, 2011)

Magic Hammer said:


> How about sharing some your best spreads sheets with us? I'm always looking for better forms.
> Thanks!
> Are you close to Nashville? I used to live there.



Hey Magic Hammer,

Yes, south of Nashville. The majority of my clients live in Nashville so I frequent there quite a bit.




Here is a workbook you might like.

This take all Pdf, Jpg, or other type image file from a specific folder, and inserts the results into a worksheet as a link.

You should change a few parts of the code to set to your defaults as far as the file extension and file path. 

Here is how to change:

Press Alt+F11 to open VBEditor
In the VBA project window find Thumbs.xlsm and below that find Module1
open Module 1

Within the code you will see marked with green text where to modify.


Let me know what you think.

Jeff


----------



## Magic Hammer (Dec 11, 2007)

Thanks, Repairman I'll check it out soon, I have a HomeShow this weekend.


----------



## Rusty Nails (Apr 3, 2005)

Here's a Lumber Estimator for you use and comments for improvement....

https://docs.google.com/spreadsheet/ccc?key=0AtOi2ivo5iCRdC1DOXAtSEt4dFpBTWJsRkFwdnRwYkE&hl=en_US#gid=0


----------



## WildWill (Jun 6, 2008)

Rusty Nails said:


> Here's a Lumber Estimator for you use and comments for improvement....
> 
> https://docs.google.com/spreadsheet/ccc?key=0AtOi2ivo5iCRdC1DOXAtSEt4dFpBTWJsRkFwdnRwYkE&hl=en_US#gid=0



*You need permission to access this item.*


----------



## Rusty Nails (Apr 3, 2005)

It's fixed Wild Will, check it out.


----------



## jb4211 (Jul 13, 2010)

Repairman615 said:


> I don't really know another way. Basically it is just how I started doing it and haven't evolved.
> 
> How do you Invoice?





cargin2 said:


> jb4211
> 
> I use Excel for invoices because it makes the most sense for the organization of my job information.
> 
> ...



I use to use Excel, but it was more time consuming and required multiple steps to get the results I wanted and needed.

I needed an accounting software, but as my business is small and I am the only employee, I was hesitant about dropping $400 on Quickbooks. So, I bought Quicken Home/Business for about $89.00. I only use it for my business. Have it used for both seems stupid, IMHO.

Quicken keeps track of your clients and their info, proposals, account payable and receivable and a lot of other useful info. Anyone who has a business needs accounting software, there's no getting around it - period. Try Quicken. I think you can try it risk free for 30 days too. You have nothing to lose. 

I'm sure you wonder how you survived without it.:thumbsup: 

http://www.amazon.com/Quicken-Home-Business-2011-Version/dp/B003YJ78JA/ref=sr_1_3?ie=UTF8&qid=1325888511&sr=8-3


----------



## cargin2 (Aug 2, 2011)

jb4211

We use Quicken for accounting. DW does that. She is in charge of outgo. I am in charge of income.

How do you figure estimates in Quicken? How do you figure up the invoice amount in Quicken?

I can look around in Quicken but I did not think it did the functions I just described.

Rich


----------



## hilldawg (Aug 18, 2011)

Cargin,

I think you're right that Quicken doesn't do estimates like you'd expect (unless it's changed since I last used it). I had to do my estimate in Excel first and then transfer the data over to Quicken for it to work.

Now I'm using QuickBooks and a program that works with it directly so I dont have to type it all in myself.


----------



## cargin2 (Aug 2, 2011)

hilldawg

Now I'm using QuickBooks and a program that works with it directly so I dont have to type it all in myself.

What program are you using that goes along with it?

With excel I build all type of side things to go into a workbook.

For Instance in my Roof workbook I have a column for eave and rake. Eave x Rake + SF of west roof. Then I do a series of roofs like that to achieve the SF of the roof.

Then the workbook asks me about a dozen questions like number of layers, LF of ridge cap, LF of valley and so forth.

After that Excel figures all the materials and the basic labor. Then I adjust the labor according to the difficulty of the roof.

For example from the the number of Sq you can program Excel to figure tar paper, shingles, number of nails, tear off and installation. From the eave and valley you can figure metal edge, I&W and # of valley tin.

Similar methods can be used to build workbooks for vinyl siding, garage framing and so forth.

The roofing workbook has a separate tab for past roofs and their cost versus the actually time it took to do it.

I don't think Quickbooks can do that kind of stuff.

If possible could you show us what your estimating sheet looks like?

Rich


----------



## mike86 (May 30, 2011)

You know of any good VBA books or tutorials.


----------



## cargin2 (Aug 2, 2011)

mike86 said:


> You know of any good VBA books or tutorials.


Are you talking to me?

On You Tube there is a college course taught called Excel is Fun (that is a link but it is not underlined.

I think there are nearly 1000 videos.

I don't know what VBA is. Repair man gets into that.

I just do excel on the surface. I'm techno-simple

Rich


----------



## jb4211 (Jul 13, 2010)

cargin2 said:


> jb4211
> 
> We use Quicken for accounting. DW does that. She is in charge of outgo. I am in charge of income.
> 
> ...


In the menu bar, click Business, scroll down to Create Invoices and Estimates, scroll down to Create Estimate.

If you win the bid, own that up are Create Invoice from Estimate. Email Invoice, get check, cash check, buy beers for all CT members:clap::clap:


----------



## cargin2 (Aug 2, 2011)

jb4211

I don't mean to be rude, if I am forgive me. I didn't ask you how you type the information into Quicken.

What I want to know is how do you figure the billable hours? How do you keep track of invoices and returns from suppliers? How do you break down the parts of your billable hours that are part of the Bid and what are extras?

Calculator and legal pad?

I just did an invoice in Quicken. It has Item, Description, Qty and Rate. I don't tell my customers how much I charge per hour. I don't tell them how much the door, and the lock and the foam and the Tyvek flex wrap cost. 

I give them 2 numbers. Labor and Materials and a description of what was done. Many times I am sending pictures through e-mails to the customer of the different phases of the job or the degree of rotten wood.

I don't mean to be rude. I have learned new methods and changed many of my practices after having similar discussions on a forum. I am just trying to have a conversation.

Rich


----------



## jb4211 (Jul 13, 2010)

cargin2 said:


> jb4211
> 
> I don't mean to be rude, if I am forgive me. I didn't ask you how you type the information into Quicken.
> 
> ...



I don't think you're being rude at all. 

I give my customers two numbers too: labor and materials. I do show them my labor rate. I keep a calendar detailing where I was and what I was working on and how long it took doing that.

I did contemplate keeping my labor rate hidden a bit. But, in the end I decided that I wasn't hiding anything _(not implying you or anyone else is or was_), and I didn't care if they new what I charged per hour. Also, I give an estimate based on an educated guess out the materials and the amount of time it will require for me to complete the project. If there are differences between estimate and invoice, that was discussed prior to moving forward so that is an issue as the customer was already aware.

I do tell my customers what I charge for materials too, not every screw but larger items: the door, and the incidentals are listed as "misc". I also don't care that they see I charge more for that door than they buy it for at HD or Lowes, etc. _No I know "I don't care" seems harsh, but for lack of a better term._

I used to use Excel but in order for me to know if I made money at the end of the week, month, quarter, year I had to go back through my Excel sheets and add everything up.

I'm not sure if Quicken is the end-all-be-all, but I think some type of accounting software is essential.


----------



## hilldawg (Aug 18, 2011)

cargin2 said:


> hilldawg
> 
> Now I'm using QuickBooks and a program that works with it directly so I dont have to type it all in myself.
> 
> ...


cargin,

Im using an online program called Jetstream (www.jetstreamapp.com) to do it. They have a database where you put in your items and its material cost, labor cost, etc. So for example Ill add "rubber roofing" to my estimate and then I plug in how many square feet or rubber roof Im doing.

When I finish it sends over to Quickbooks and builds an estimate there. Ive been using this for about a month and its working out ok so far


----------



## cargin2 (Aug 2, 2011)

hilldawg said:


> cargin,
> 
> Im using an online program called Jetstream (www.jetstreamapp.com) to do it. They have a database where you put in your items and its material cost, labor cost, etc. So for example Ill add "rubber roofing" to my estimate and then I plug in how many square feet or rubber roof Im doing.
> 
> When I finish it sends over to Quickbooks and builds an estimate there. Ive been using this for about a month and its working out ok so far


Hilldawg

Interesting. So you are using a cost book. I have used National Estimator. It currently costs $100 a year. I don't believe is is an online service. You can download the cost books on to your computer. There is a free cost book called Super Build.

Most insurance companies us Xactomate or something like that.

How much does Jetstream cost?

Rich


----------



## jproffer (Feb 19, 2005)

mike86 said:


> You know of any good VBA books or tutorials.


Visit these sites:

MrExcel

VBA Express

And there are many, MANY more but those are the 2 I visit most often.

They both cover EVERYTHING you could imagine about Excel and VBA


If you have specific questions related to construction/estimating/etc you can ask here and one of us might better understand what you're up against. 

But for general questions/problems the MVPs on MrExcel and the regulars on VBA Express (many of whom are the same people) are hard to beat.

Speaking of the same people on both sites....they kinda frown on "cross posting". If you post the same question on both of those sites, some of the members get a little pi$$y. I personally don't see why, but they do.


----------



## Repairman615 (Jan 10, 2011)

jproffer said:


> Visit these sites:
> 
> MrExcel
> 
> ...


Those are the same two I visit as well. :thumbup:

Mainly at VBExpress I just play games though...:thumbsup:




About the cross posting:

Here is a link I have seen several times... 

http://www.excelguru.ca/content.php?184

I am not uptight about it, however I think it is good when someone upfront tells you this question can also be found at "______.ex".


Jeff


----------



## flashheatingand (May 3, 2008)

Good to see an old post like this come up. To each their own. Being a simple simon guy, I find excell to be a great program. It's so simple that *I* can tweek a system that works for me. Excell has also helped manage my paper-work immensely.

Quick Books I am sure, is great. I think their advantage is that they take charge, and you follow their lead...in an efficient manner. Probably several other advantages. But, if you have mastered 9th grade math skills (something most people here have, but not something to be taken for granted), and, understand the concept of cut & paste, there is no reason one could not use Excell to his her advantage.


----------

