people using 'set print area' on an excel spreadsheet

[thecolonelbumminganugget]

It annoys me so much I have to leave my desk and make a cup of tea to calm down everytime someone emails me a spreadsheet where they've done this!

There is no justification for this. Either:

A - you have set the important information to print and everything else is backing information. In which case you need two tabs, one with the summary, the other with the backing so anyone who wants to can trace it through but the important information is summarised on the front sheet. Or;

B - it's all equally important but YOU only needed to print part of it. In which case either select cells and use print selected or use clear print area before you save it in a shared location or forward it on. If it is the case that the bit you needed to print is the same bit everyone else will need then I refer you to point A above.

All that happens is that you send it on, the recipient prints it to read, or worse still when they've added their own work to it and printed it, the bit they wanted didn't print because it's not in the print area you dictated, they throw it in the bin, swear at you behind your back and have to go back to their desk, clear print area, and print it again.

I know I'm not being unreasonable when I say the only reason to do this is because you hate everyone you work with!

(Oh god that feels better)

[unlucky83]

Drunctioning You can also do something similar but different to what random said - depends why you want to know the total...
So I would put a list of the months and in the cell next to each of them do = and click on the relevant total on the separate sheets and then sum them.
So you have a breakdown on one sheet of each months total expenditure and the running yearly total...
And you can put those figures anywhere on the sheet compared to the sum box ...
Actually a couple of useful basic tips - I'm self taught (and slightly out of date with Excel now) but...
It took me a while to click this but it is handy - You can put the total of a column sum at the top of the column and add things to the bottom - just make sure you put a border or something at the point you finished selecting for the sum -so you know you have increase the selection if you list too many items. This means that if you want to see a total you don't have to scroll a random distance down a page to see it -it is at the top.
(Although Ctrl and down arrow takes you to the last entry on the page before empty cells - as does ctrl and left arrow and right and up)
And do you know about the $ sign? Don't know if I can explain this very well but if you are dragging cells to copy formulas if you use
=$A1 in a formula will fix the A column, but the rows will increase.
=$A$1 and the dragged cells will all use A1
=A$1 and the columns can change but the row won't
(it has a proper name but it is has slipped my mind - 'something' reference)

[wasonthelist]

TheGruffaloMother

Curious to know what sort of organisation requires so much printing of spreadsheets to function - what are the print outs used for?

[RowenaDahl]

I hate it when people DON'T set it up.

[TheGruffaloMother]

wasonthelist, why they were printing it at all was another battle I repeatedly lost.

[RandomMess]

unlucky - is it "absolute" reference when you but $ in?

TBH if I was creating a summary it would end up full of vsumifs formula and stuff. So many different ways of doing the same thing...

In basic terms how you would do things on one tab you can pretty much do on a summary you just click on the tab you want and then the cell.

Just remember to create a copy of the work book before you start and the undo button is very useful indeed!

I missed do the company finances in the handwritten ledgers once the spreadsheets came in

[BoulevardOfBrokenSleep]

I do kind of love Excel, having trained as a scientist in the dark ages. It adds stuff up for you, works out the average, plots a chart... ok, it has no common sense about deciding what to print, but you can't have it all, right?

DrunctioningFunk, this is the sort of formula it sounds like you need to use?

[StatisticallyChallenged]

Previous role I had to produce MI reporting which contained status reports on projects and actions. These were then discussed at a weekly meeting. The reports had quite a few columns (which couldn't be cut down, apparently...) and several tabs and nobody would read them on a computer - I had to print off about 15 copies on A3 every single week.

For our own business the regular printing is for registers which need to be signed.

[unlucky83]

random -that's it an absolute reference.
My other tip is if you select multiple tabs (holding the shift key down whilst you click the tabs) - whatever you do to one sheet will happen to all the selected ones -handy if you have identical sheets for eg months and think I wish I had added an extra column for x etc (but remember to deselect afterwards ...)
boulevard I was a scientist. I remember having to explain to someone in the early/mid 90s that in Excel eg 1.45E+5 was just scientific notification...
Also working somewhere in the early 00s -in academia! - we had a machine that analysed samples and we got a print out - pages long, hundred of figures. People would go through their print outs, section samples off and work out averages etc. then transfer a summary by hand into a lab book - the more advanced would manually enter the figures into excel to work out the averages.
Apparently you could copy the data straight from the machine to a (floppy!) disk - but was told it wasn't much use - it came in a weird format, they didn't have the software to open it ...that would be a cvs...
I swear they thought I practised the dark arts when I could have my data analysed in less than 20 mins...rather than hours (In fact someone insisted on going through my print outs the first few times to double check that their manual version agreed with my analysis...but still I was the only person who did it it was just too complicated )

[DadDadDad]

In my profession's monthly magazine, they have one of those tried and tested Q & A formats: meet an upcoming member of the profession with them answering questions such as "who would you want to be stuck on a desert island with?"

This being actuaries, one of the questions is "Favourite Excel function?" They always have an answer.

I'm not sure I could pick just one that is my favourite. OFFSET? It's amazing how much you can achieve with that.

[thecolonelbumminganugget]

One more time for the cheap seats at the back... I'm not talking about formatting to print. I'm talking about cutting things off that other people want.

Here's my example, every month we reconile our company financial position. The person who compiles the information looks at the balance sheet so cuts off the profit and loss information from the print area. It's my job to make sure the p&l movements that make up the balance sheet total aren't bollocks, hence it's annoying when I print it and only balance sheet pops out . Just one (over simplified) example. I am not talking about formatting. The balance sheet is beautifully formatted to print.

[wasonthelist]

OK, I know it's a thread hijack, but may I reserve a special place in hell for all those people I work with who insist on creating spreadsheets for stuff that has no mathematical or numeric content at all please?

[wasonthelist]

thecolonelbumminganugget why does it need to be printed? What are the paper copies used for?

[bakingdiva]

My DH bought me an I ❤️ Spreadsheets mug......it's so true! The excitement when I found the SUMIFS formula.....upto 27 conditions, it doesn't get better then that!

[thecolonelbumminganugget]

wason mostly because I work in 1950 where somebody signing something off means exactly that, someone actually signing a piece of paper. I have won some battles over excessive paper but I'm yet to win the war!

[ComputerUserNotTrained]

I bloody ❤ this thread.

[DadDadDad]

baking - I've got that logo on a notebook that's on my desk at work (and yes, my DW bought it)!

And further to my previous post, you're going to love this guy: www.theactuary.com/students/actuary-of-the-future-june-richard-cronin/

[thecolonelbumminganugget]

wason also yes you can, particularly if these are people who are writing information that relates to something in adjacent cells and then don't align the text consistently in those cells.

[wasonthelist]

work in 1950 where somebody signing something off means exactly that, someone actually signing a piece of paper

Good grief! Do you have a tea trolley and luncheon vouchers too?

[wasonthelist]

I bought myself an "I love spreadsheets" mug but purely in an ironic way - in the sort of outfits I work for they are used as badly managed, poorly formatted to-do lists designed to sap any joy from work.

[wasonthelist]

Oh, and their primary purpose is as a make-work for their jobsworth originators who can then bleat about how hard they work.

[ClashCityRocker]

wasonthelist all our client files are in paper format as well as electronic.

I'm a tax advisor, and whilst I don't tend to use excel for databases as we have different programmes for that, we use them for working papers which I believe is pretty common practice in the accountancy field.

Some firms have gone paper-less but many still use a similar system. The working papers need to be printed for the file to create an audit trail. A complete file will often have various bits in, including raw data from the client, which could be sage printouts, the clients own spreadsheets or scribbles on the back of an envelope.

To be honest at the one firm I worked at that was going paperless, it was a total pita because it wasn't well managed at all.

[DrDreReturns]

unlucky83I can well believe your story. I've heard tales of people in academia using really antiquated techniques. e.g. ancient versions of FORTRAN
Incidentally - I'm a computer programmer, but I got into programming initially due to repetitive Excel tasks which I automated via VBA.

[ArgyMargy]

YABVU to be printing stuff at all - this is 2016. FFS.

[wasonthelist]

ClashCityRocker

That is interesting - when I had a small limited company I sent my figures to my accountant electronically although they did produce paper copies of my year end accounts - in fact I didn't need them. I kept my own paper trail of receipts etc in case HMRC ever came knocking so I get that - but surely the idea of a big paper bundle is largely unnecessary now?

When I worked in IT customer support in the 90s we had some paper files on customers - but that was the last time - I work for an IT outfit now and we have almost no paper records of anything.

I don't see how pdfs from the spreadsheet saved to storage create any less of an audit trail than paper prints? I accept I may be missing something.

[wasonthelist]

DrDreReturns

I got told a tale at one company I worked for where they apparently had one of those long in the tooth techy types who did all the "interfaces" from the payroll system. When they were changing systems he was very reticent about the nature of the interface programs he was supposed to be maintaining - it transpired he'd been printing off the figures and typing them into the other systems for years, and keeping it a secret that he had no clue how to write or maintain any kind of electronic interface.