Dating problems...let Excel help you

Don't worry, this hasn't turned into a 'Dear Mary' column, we are still talking Excel here. But lets take Excel into the romantic realm for a moment, and see what magic it can work. Take this scenario of young love:
Mike and Michelle started dating on 12 December 2011. Michelle is adamant they have been together for 138 days, Mike says 137. Why the difference? It is all in the semantics (of the formula, that is)...

Mike is doing a simple formula of subtracting the current date from the day they started dating. This gives you the answer of the number of days BETWEEN these two dates. Michelle is using a slightly different formula, and is INCLUDING the day of the start date.

This one day difference probably won't break up a relationship, but it could cause a few problems when considering something like overhead cost per day of a production cycle.

Example: If you start production on the 1st January, your factory doesn't stop for weekends, and your overheads for January were €15,000, consider the following in your calculations:

Now this is the type of difference that could cause you relationship problems, with your accountant anyway!

Lets take this one step further....your factory does shut for weekends....now how do you calculate the number of WORKING days, in order to calculate your overhead cost per working day?

Like this:

The Networkdays formula calculates the number of working days between 2 dates. You can either ignore holidays, or tell Excel when the holidays are, and then include that range in the formula too, depending on whether the factory closes for holidays.

The cost results for all these formulas show that while we may jest about Mike and Michelle squabbling over one day, getting the right date formula is crucial when calculating business costs.

This week, our subscribers to our newsletter are getting even more handy tips on dates, why not sign up? Click here.. http://www.excelangels.ie/page_8.html

See you next week, and again, please feel free to ask for any topics you would like covered.

Autofill your life....well, at least your spreadsheets anyway

Our tip this week is all about how to use the 'Autofill' function. This little gem can seriously save you hours upon hours of mundane and mindless speadsheet input.
The best way to show you is through example, so imagine you are doing your annual household budget.

First off, you want to make a list of annual expenses you need to budget for. So you can manually type 1 to 20 in a line going down your spreadsheet. Or, you can type a 1 into Cell A3, a 2 into Cell A4, and then highlight both cells until they are surrounded by a thick dark line, like so:

Hover your mouse over the bottom right hand corner of the highlighted cells, and a little cross will appear. Drag that down for as many rows as you need, and hey presto, you have a list from 1 to 20, or 3064, depending on how many expenses you have.

That was the easy bit. Now you think of all your expenses that you have over the year, and type the descriptions into column B, and the annual cost into column C.

To calculate how much you will spend, or need to save every month, you need to divide these annuals costs by 12. Do this in column D, but again, you won't need to do it in each cell. Autofill steps in to help again.

Type the formula =C3/12 into cell D3. Hover your mouse over the cell D3 until it is highlighted again, and the cross appears. Double click on the cross, and your formula miraculously appears down to the bottom of your list. No need to even drag it down, but you can if you would like to.

As you can imagine, when you have 10 052 lines of data, using that double click function can save you hours of mental tedium!

Some other points on Autofill:

• You can drag across as well as down
• You can create all sorts of number lists, not just consecutive numbers. For example, if you only want even numbers, type 2, then 4 into the next cell, drag down and you get an even numbered list.
• When you drag down, you will notice a little box on the bottom right hand corner once you've completed the drag down. Click on this, and it gives you a pop up menu with even more choices for your Autofill pleasure.
• You can also use Autofill for dates, months and years. However, that can get a bit more complicated, so we will cover that next week.

Hope you found this useful. Again, feel free to suggest topics you would like me to cover.

Happy spreadsheeting!

Getting out of Excel gridlock

Hi

Welcome to my blog. I'll be sharing a useful tip about Excel every week...yes...really! I think Excel is one of the most powerful tools you can use in business, and people just don't know enough about it to get the most benefit from it. I hope to share a bit of my knowledge with you on a weekly basis.

If you ever want to know more, check out my website www.excelangels.ie , and  we can set up a training session.

So...the first tip....I'm going to start on a really easy one, but one that I use all the time. Because I like my spreadsheets to look pretty. And I hate the gridlines.

So if you would like your spreadsheet to go from looking like a scary gridlocked page of numbers, to a neat, plain blank page with some official and "they have to be right they're so smart" figures, here is what you do:

In Excel (2007 or 2010), click on the "Page Layout" tab on your menu ribbon. Midway through the ribbon you will see "Gridlines". Just click on the little box next to "View", so the tick no longer shows.

Et Voila. No more gridlines.

Now, even more impressively....you can do this with the click of 3 letters. No need for a mouse:

Press Alt, then P,V,G.

Cool, isn't it?

If you're missing the gridlines, just repeat the steps above.

This was a super simple tip to get us started. Let me know if there are any Excel topics you want discussed. I'll be happy to oblige.

Till next week,
Vicky