So many to choose from but nice simple one just to get the ball rolling.

If you have a column of figures to either the left or right of a new column and want to autofill, rather than dragging the bottom right of the box to the last cell just double click on it to autofill until it reaches a gap.

To auto increment fill in the first couple of values, highlight both cells and then double click the bottom right hand corner to auto increment rather than simply autofill to the last entry to the left or right with the same value.

Note that if you have a value in the columns to both the left and the right Excel will choose the column to the left to autofil against.

I'll keep adding to this excellent thread (mostly cell formula's methinks) but just give other people a chance to chip in their own entries.

Ben, do you think best to put a cap on one tip per reply so as not to overwhelm people? Sure that between us all we'll keep this thread towards the top of the recent posts list for the next couple of weeks at least!

kind regards,

Shaun.

OK I love Excel so here we go, if you want to have the current date showing on your sheet every time you open it enter this into your excel sheet cell =today() this will display the current date. Have to think of more now.

Alfred wrote:

---

OK I love Excel so here we go, if you want to have the current date showing on your sheet every time you open it enter this into your excel sheet cell =today() this will display the current date. Have to think of more now.

---

Up until a few months ago I had been using =now() and then formatting it so it was just the date and not the time. Doh.

Ctrl ; will quickly add the current date to a spreadsheet that won't update the next time you open it.  

Ooh I like shortcuts on Exel, is it sad that I'm looking forward to trying some of yours Peasie??

I always use the Ctrl Shift and the Down or Up arrow key combination to select all values in either direction. So for example I'd use Ctrl Home to go to cell A1 then Ctrl Shift and Down to highlight all values in the A column. (Normally because I'm going to copy and paste the data elsewhere).

I also do lots of reconciliations and the simplest way of checking data (especially when codes are used) is by subtracting cells from each other, for instance =C1-F1. Double click bottom right to copy the formula down the column and where the answer is not 0 you know there's a mismatch in the code.

Ok got another one, if you right click on the tabs you can change the colour of them as well as move or create a copy of that excel sheet you are working on.

woody88 wrote:

---

Thanks for the tips,

It looks like excel is a good topic and one that we can all learn from.

My tip of the day is: the =sumif( ) formulae - as this has allowed me to stop using an excel cashbook that lists each expense code in a seperate column and allows me enter each lines expenses into one column that you can list the expense code and then have a sumif formula somewhere else in the spreadsheet to show you how much was spent on each individual expenses code.

Have a great day,

Ben

---

I made exactly the same point over on the ICB forum yesterday. What a moment it was for me when I discovered that formula. So simple yet so useful. 

Hi Peasie,

Sorry to steal your thunder! Must be having a moment as I even read the post. Got to say though, that I don't think I could go on without some =sumif in my life.

Have a good afternoon.

Ben

Alfred wrote:

---

I dont think that I have actually used =sumif in any of my spreadsheets yet, I normally use a pivot table to help me group this type of data making it easy for me to see the totals.

---

On the other forum I said there was probably an even better function than SUMIF - it was pivot tables I was thinking of although I've yet to progress to them. If I remember correctly I didn't like them because I think everything had to be in order (alphabetical/numerical - can't remember now). I haven't tried pivot tables since I got Excel 2010. I jumped from 2000 to 2010 and had a brief stop at 2003 or 2007. 

I love the SUMIF formula too - it's like an accounting rite of passage (I think I have even asked an interview question on it, many moons ago)

I also like CONCATENATE to join together the contents of different cells (and also, the other text functions, like LEFT, RIGHT, MID come in handy.
Some of the array functions are very nifty (although they remind me of matrices from school....)

I generally have several "check zero" formulas and if I'm feeling particularly colourful, I sometimes use conditional formatting that makes the cell change colour if the check zero isn't actually zero.

SUMIFS takes things a little further. You can sum figures based on more than one criteria. For example, you can turn sage data into a meaningful month by month P&L by using formula such as sumifs date equals x and account code equals y....etc.

A simple (maybe too obvious??) one that I use a lot is simply 'save as'!! Rather than saving the file, you can scroll down and choose 'pdf'. This will provide a pdf of the tab(s) that you have selected. Handy if you want to send something to someone without them seeing the entire workings!!

hi Michelle,

the name of the other workbook goes in single quotes followed by an exclamation mark.

For example, if you have five worksheets. Totals, Q1, Q2,Q3 & Q4 and you wanted the total for i4 to i28 in Q2 then you could enter the formulae :

=SUM('Q2'!I4:I28)

Personaly in that situation O would be more likely to total within Q2 then auto link the result in the Total worksheet. :

='Q2'!I29

Hope that was what you were looking for. If you wanted to know about summing between spreadsheets that's similar but you just need to know that all of the spreadsheets that you are working with must be open at the same time,

kind regards,

Shaun.

We've still got miss Ellie, Sue Ellen and Ray Crabbs on the site. Makes me think that I should never have moved away from JR.... So prepare for the return.

 

And he's back....

 

Wonder if we'll get Amanda to go back to the poison Dwarf now?

-- Edited by Shamus on Monday 23rd of April 2012 04:24:32 PM

Thank you for your replies I will give it a try and see how I get on.

Thank you again

Oooh, I love this thread and I feel slightly responsible as I think it was my 'non sage question' on another thread that was quoted in the orignal post (there was certainly a comment about non sage questions on one of my posts!!). I've got a big day of Excel tomorrow and I'm looking forward to trying some of these short cuts. Think I might need to print them out and stick them to my screen to remind me to use them until they become second nature.

And the thread also comes in as a handy reference point for Characters from Dallas as well!

Have fun tomorrow.

Shaun.

I only discovered the Vlookup function a few days ago. It's great for price bands that don't necessarily fit to an exact formula. There are new (and shockingly high) postage prices coming in on April 30th, and I needed something that would calculate and display what it would cost to post our products to customers so that we could decide what to charge for P&P.

I can hardly believe that I used to do price bands by using nested IF functions! (They were extremely cool, though.)

Peasie has already said what would have been my number one tip: "When I want the total in another soreadsheet I just put = then click with my mouse the cell with the total then enter." This will seem obvious to some people, but I was a minor expert on using formulae before it dawned on me.

I have a question for all the experts present:

Is there any way of making the "Sheet1", "Sheet2" tabs on a workbook display vertically along the side of a workbook rather than horizontally along the bottom? Failing that is there a quick way to navigate from one sheet to another if you know the titles? I already know that you can go quickly to the first and last sheets using the little arrows with vertical lines on the points (forgotten the proper name for them) at the bottom left of the screen.

I have several workbooks that have quite high numbers of worksheets, and it is a real pain only being able to see three or four at a time.

Surely "cut" then "paste special", not "copy", if you want to show the values in columns *instead*.

As suggested by Rob, it's likely to be your autocorrect feature.

Assuming that you are using Excel 2007 or 2010 then go to the big round button in the top left corner than I've got no idea what we're supposed to call it.

Go to Excel Options

Proofing

Autocorrect Options

In the box replace text as you type it will have things like (c), (r), (tm) etc.

You can either untag the option to replace as you type or remove the options that you don't want converted.

Hope that helps,

Shaun.


p.s. edited because I really must proof read my notes before posting... Seems that almost every letter d (and only letter d) was displaced over by one character to an f.... Well, either start proof reading or get a keyboard with bigger keys for my dinosaur fingers (and matching brain).

-- Edited by Shamus on Monday 4th of June 2012 04:50:06 PM

Click the undo button or Ctrl Z, will return it to the bracketed letter you wanted.  This also works in Word.

Sylvia

Thanks for mention Peasie

We are Excel and Sage Developers and are happy to help with general queries for either one.

An example of what we can do is on this you tube video

Automating management accounts in Excel with a live link to the Sage data in 2 mouse clicks and no install - have a go on your clients for free if you want to see it in action..?

Thanks

Optegra Support