Excel tips

[Bwoodbury]

So I'm graduating from college imminently and I have some time that I want to devote to learning Excel. Basically what I'm looking for are some Excel hints. What are interesting things that you've learned to do in Excel that you find particularly helpful or interesting?

[valence]

You probably already know at least some of this, but the features in excel I find most helpful are:
-the merge and centre button: for creating pretty titles, mostly
-the orientation options (under alignment): generally use for making attendance sheets
-formatting things as tables, with headers: aside from the automatic colour coding, when you have headers you automatically get a little box next to the header that allows you to sort the table by that column very quickly
-freeze panes (under view): allows you to scroll one part of your document without having the other parts scroll.  Helpful if you've got a really long list and start forgetting which column hold which information (thinking of things like costume sizing charts)

[kmc307]

Some of the ones I use frequently are:

Conditional formatting.  Great for picking out data from a big list that meets a certain set of criteria.

Data validation.  More of a financial feature, but one useful thing for SMs is you can create a "drop down" list.

Group.  Allows you to group columns or rows of cells together, and easily collapsing and expanding the group to hide or show the information.  Great for contact sheets where some information may be confidential - you can easily collapse all sensitive info, .pdf it or print it, and none of that information will show.

[M Peters]

If you want to put multiple lines in a single box (say, for call-times in a performance report), hit alt-enter after each item instead of enter.

That's for PC.  I'm not sure what the corresponding command for mac is.

[nmno]

If you want to put multiple lines in a single box (say, for call-times in a performance report), hit alt-enter after each item instead of enter.

That's for PC.  I'm not sure what the corresponding command for mac is.

Mac is Command+contol+ return  (Happiest day when I discovered that feature...  Simple, but eliminated a lot of formatting for my paperwork)

[iamchristuffin]

... automatic colour coding...

Do you know how to do this in earlier versions than Excel 2003?

Chris

[valence]

Do you know how to do this in earlier versions than Excel 2003?

I've never personally done on older versions of Excel, but http://www.techonthenet.com/excel/questions/cond_format2.php looks like a way to do it, though it's not the simplest of methods.

[On_Headset]

Concatenation is very useful. What it does is join the contents of several cells together, and you can add strings of text as well. So if column A contains a list of your actors, and column B contains a list of roles, =CONCATENATE(A2; " will be playing the role of "; B2) would generate your cast list. ("Robert Webber will be playing the role of Juror 11")

The real beauty of it comes from using it for grander things, such as mass-mailouts:

=CONCATENATE("Hello, "; B2; ". On behalf of the director, I am pleased to inform you that you are being called back to interview and audition for the role of "; C2;" in our production of Marat/Sade. If it is convenient, we would like to see you on "; D2; " at "; E2; " in room 213 of the Swan Theatre at 441 Main St. We would like you to prepare two monologues and a song suitable for "; F2 ;", none of which should be from Marat/Sade, and we will also ask you to do some cold reading. The interview and audition should take no longer than 30-45 minutes. If you are interested, please respond to this e-mail or telephone the production office at 723-441-0404, extension 10500. Thank you for your time, and best of luck in future!")

Would generate:

Hello, Margaret. On behalf of the director, I am pleased to inform you that you are being called back to interview and audition for the role of Charlotte Corday in our production of Marat/Sade. If it is convenient, we would like to see you on July 12th at 11:30 AM in room 213 of the Swan Theatre at 441 Main St. We would like you to prepare two monologues and a song suitable for an upper-class woman of 25 years old, none of which should be from Marat/Sade, and we will also ask you to do some cold reading. The interview and audition should take no longer than 30-45 minutes. If you are interested, please respond to this e-mail or telephone the production office at 723-441-0404, extension 10500. Thank you for your time, and best of luck in future!

If you're someone like me who finds Excel easy to work in in terms of records management, this saves a lot of time and effort.

[megf]

On_Headset, that's beautiful! I may have to use that.... oooh...

A simple feature I've found in Excel (or OpenOffice Calc, which is what I actually use) is the formulas. Particularly when contracts limit your total weekly rehearsal hours, it's a great help.

For instance, on a 29-hour reading: you know your span of day is 7 hours, and each actor can work up to 6 hours within that. Set up a simple grid - and let's say you use =SUM(A2:A7) to total an actor's hours in A8. My current reading has us juggling a lot of widely varied schedules, so rather than rely on memory or my own dodgy math skills, I've also used =SUM(29-(A8)) to track total hours remaining for each actor, so I can sit with the team and have a quick reference as we plan each day.

[sgoldsbo]

I work in a day job where I have to manipulate a lot of data in Excel - and I have found vlookup to be invaluable. Basically you tell Excel to look for a certain value anywhere in any table and pull corresponding data into the cell with the forumla.

For example, if I had one spreadsheet with prices, and one spreadsheet with quantities I could tell Excel - look for this Widget in the prices spreadsheet and pull that data into the spreadsheet I am currently working on.

There are some great tutorials online. I haven't yet found a use for it in my SM duties but I use it almost daily at my real people job.

[Thalia]

My bf's a writer and he uses Excel to lay out graphic novels. I know that sounds weird, but he first blocks out a grid, and then lays in the panels, texts, and sometimes primitive drawings for the artist to follow. The other day, I saw him lay out a rainbow-colored time line for a fictional family from the 1700s to the present.

That got me thinking about creative ways to use spreadsheets besides mundane scene management. I took head shots of the actors and when I mapped out my scenes, I dropped in the head shots like icons. The actors asked for extra copies to send their families.

We had a production with lots of complex wiring and cables snaking everywhere. Like everyone, we color-coded cables, but I used Excel's borders and patterns to create a wiring chart. In other words, I wrote in 'left cannon, red stripe, squib E2' AND drew in a fat red stripe in a couple of cells so managers and prop handlers could glance at it without having to put on glasses. The same might be done for ropes and lines, although I haven't tried it.

I still use a draw program (like Canvas or NeoOffice) to sketch ideas, when when I want something everyone can view, I turn to Excel. It's not as fancy, but almost anyone with a computer can open it.

[Nic Sedivec; SM]

I have found that "repeat row/column" has made my life so much easier in terms of formatting. The newest version of Office has a button for it under 'layout.' I am afraid to say that I forget how to make this happen in an older version. You could easily find it under the search or help I'm sure.

[catalinacisne]

This might not be news to anyone, but I jumped up and down with joy when I discovered it: If you need to change the width or height of all your cells to one uniform size, select all of them using the arrow in the upper-left corner and drag the width/height of the first cell to the size you want. All cells will adjust to that size. Or if you want all columns to adjust to fit their widest cells, select all and double-click on the break between the first and second column headers.

If memory serves, "Repeat row/column" was located in File>Page Layout>Sheet on older Excel.

[ChaCha]

i love this thread.
bring on your excel tips!