your favourite ms office process or tool

[StealthPolarBear]

I expect this thread to run to thousands of posts :)

Access compact and repair. Lovely.

Does it? Wow! I though I knew all the shortcuts till I came on this thread.

We used to do the setting people's autotext for them thing as well, so that when they wrote their name it came out slightly wrong or something. I've never used it in earnest though. Actually, I do have two blocks of text to insert into all these docs I am updating, at the moment I've got it on the desktop in a separate doc, Autotext would be a good way of doing it.

I'm normally very against auto spellcheckers etc, I have the thing with the green and red lines on, but only switch the thing which shows all your paragraph marks etc on to check things.

toothbrushthief dont have Excel 2010 at home so cant remember exactly, but to highlight duplicates look in conditional formatting, there is an option there. also, after you have highlighted them you can select them using filter by colour.

Thanks Lindax

Oi geeks. Don't think you getbthe night off just cos its jubilee weekend

Shift+f3 in word or PowerPoint or outlook. Do it, it will change your life!! . [saddo]

Ps I really love pivot tables

Ooh I do know that one and agree its marvellous

Column splitting and concatenate. I always need to delete the original columns after merging and I found out that if you copy the merged column, then do paste special and select values, the results stay intact

I shouldn't be so happy about this.

Thought of a couple more for Excel:

• hit ctrl ¬ and it will switch between displaying formulas and results

• when typing text in a cell use Alt+Enter to get a carriage return and put your text on more than one line within the cell

• if you want to type the same text into multiple cells, highlight them then type text and press Ctrl+Enter

• If you are doing a load of SUMIF formulas in Sheet1 using the formula builder it create the formula like this:

=SUMIF(Sheet2!E:E,Sheet1!E7,Sheet2!G:G)

remove the sheet reference from the criteria part ( Sheet1! in example above), or you will get formula relative reference problems later if you sort the data in Sheet1.

• Also try using IF statements with AND's or OR's to save nesting to many IF statements =IF(OR(A1="A",A1="B",A1="C"),TRUE,FALSE)

love this thread

hope no-one minds me dragging this thread up again, Ive found it really useful as am currently studying for MOS to update my skills and was wondering if anyone knew of a place where I could download a practice MI or similar thats fairly detailed so I can have a play with it? The course Im doing is great but there is nothing like doing it for real to check understanding of it.

Vlookup in excel

Hallo
We have moved this to geeky stuff so the thread doesn't get lost for-evah.
Thanks
MNHQ

Thanks Olivia :) Did you use a cool keystroke combination to do that or just the boring usual way?

I am on a Mac, so everything I do is a cool keystroke combination.

Has anyone figure out a way to make vlookup look backwards yet (so -1)? I suppose not as it'll assume the first column of the range is where you start but there must be a less clumsy method than shifting columns around

Only just seen this now it has been moved - definately the right place now.

Not sure I have time to go back through, but to do 'non-standard' lookups you can use match and index so instead of this:

=VLOOKUP($A3,OtherSheet!$D3:$D1234,-1,FALSE)

do this

=INDEX(OtherSheet!C:C,MATCH($A3,OtherSheet!$D3:$D1234,FALSE))

In fact I hardly ever use VLOOKUP as I usually need to extract information from more than one column so I often use this with a single MATCH in one column and a number of INDEX columns copied across - it recalculates much quicker than repeating many VLOOKUPs.

Thanks. I tend to use simifs for that as I usually need to add the columns up

Marking place so I can read and try these out on laptop.

Im mithering again! anyone know where I can get a test spreadsheet I can muck about with? This course Im doing is turning out to be a load of shit frankly so am thinking about using the basics of it in conjunction with some self teaching. Have tried to do my own but Im subconsciously making it easy for myself IYSWIM?

What do you mean avtest spreadsheet?
Just one with loads of data? Ons will have population spreadsheets, the nhs ic has a load with child obesity stats on - search ncmp

Yes sorry, just one with loads of different data so I can practise what Ive learned so far. Im fairly well up on Excel (self taught) but havent used it for a while, and have lost a bit of confidence for one reason or another, so decided to sign up for a MOS course that covers intermediate and advanced Excel along with Word etc. Its a good course but is pretty crap when it comes to the simulation part. Its very basic and actually tells you what you need to do in a massive box that covers 1/4 of the sheet so its hard to ignore, and the spreadsheets are very basic invoice type ones whereas I was hoping to practise on larger sets of data, what I was used to at work - 1000+ rows x 100 column type stuff.

Will have a nosey at what you have suggested, thanks Stealth

ImperialBlether to hide the formatting toolbar, go to File, Options (might say Word Options depending on the version you are using) and then deselect the check box for "show mini toolbar on selection"

I love that you can make a Vlookup return more than one column of data in a single formula. Say you want the name and surname returned, you'd normally do one VL for the name data and another for the surname data. Instead, highlight both the result cells and then setup your VL like this:
{=Vlookup(A1,TableArray,{2,6},false)} - you must have those curly brackets
So columns 2 and 6 of the table array contain the name and surname data.

But array formulae are the work of the devil DetribalisedOne

And I find "string 1"&"string 2" easier than CONCATENATE("string1","string 2").

Loving this thread.

I've never mastered pivot tables - anyone know a good idiots guide?

I still enjoy the basic stuff:

• Double clicking on the corner of a cell to copy a formula into the column if cells below
• the 'transpose' paste function
• freeze panes

And I do concatenate the longhand way because it is my favourite word

I don't know an idiots gide, but what do you struggle with?

Tbh I think what happens is I try to use them in a rush having forgotten everything I ever knew and just get muddled. I prob just need to set some time aside when I'm not in a rush to get something done.

Will come back here if (when?) I get stuck :)