Super cool computer tricks, for dopes like me.

um...that's a lot more work than it's worth...these aren't jpegs or gifs, and they still need to be cropped.

easier just to work within the excel file than have to go to image shack and browse, which you can't anyway.

since I have the formatting macro, it takes less than, oh...90 seconds to print screen and copy, and run the macro to format all 7 days.

Best to keep in a summary page so the people I send this to can also work on the spreadsheets for plan times.

You're still misunderstanding, Chai. That post had nothing to do with yours. It is simply instructions on how to post a pic from your hard drive. (I've explained that on a dozen threads, so when it came up this time; I copied it to here.

Oh, didn't know we were on a new subject.

question, why do you use so many 's?

just wonderin'

I have a basic question. What is a macro? What are they for? Sometimes my computer asks me if I want to disable macros, and I've no friggin clue if I do or don't want to. I tell him to make up his own mind, but he wants me to decide.

They are friendly laughter emoticons. It struck me funny, because there is no possibility those instructions would improve your application... so when you wrote "hmmm...I'll give that a try.
thanks"; I thought you had made a joke... and a funny one at that.

OK, I'm not a techie, so I hope I'm clear.

A macro is a tool to simplify tasks that you do all the time.
I work mainly in excel, so I'll speak from that aspect.

There are 2 ways to create a macro, either by recording one, or writing one.

Recording one is easy as pie.

In my example, where I wanted to crop an image...first, without creating a macro, I determined that I needed to crop the image 3 points on the left, 4 points on the right, etc.

ok, so now I know what I want to do....I'm going to call the macro "crop"

With the full orignal image sitting on your excel sheet do the following..

Tools
Macro
Record New Macro

The window that appears, asks you to name the macro, name it "crop"
Where is says "store macro In" you can choose to just keep it in that workbook (or copies of the workbook if you're using this as a master) or in a Personal Macro Workbook, which makes it available to use anywhere (the personal macro workbook is hidden)

You'll see you can also create a shortcut key.

Click OK

Now, everything you do, every keystroke, click etc is being recorded. You go ahead and crop your image, resize, everything you want to do with it.

Then, click
Tools
Macro
Stop Recording.


Now, whenever you want to crop a picture like that (like for my 7 days) You click
Tools
Macro
Macros
and choose the one called "crop"

or use your shortcut key.

Both those methods of running a macro are a pain in the ass to me, that's why I put a clip art picture up, and assign the macro to it, like I said in a previous post.

then, when you want to run the macro, you just have to click on that clip art, and it runs.

Try doing that yourself, it's really easy.

Just record, type a bunch of stuff, formate change colors, whatever, then stop recording.

It saves a lot of time once you get the hang of it.

Hey, I just tried it it Word, tying a bunch of garbage, and it works the same way.

If you are constantly having to type a long paragraph, you could assign a macro to do it for you in a instant.

You can also write a macro, or edit it, but that's too much to go into right here.

Oh, as far as whether to disable a macro?

That depends on if the person who created the macro wanted you to be able to use it.

If you disable the macro, which you probably didn't even know was there, you just won't be able to run it.

Sometimes I've built a macro into a file for other people convenience, but when I send it to them, I'll tell them it's there, what it does and what they need to click on to run it.

I had someone tell me over and over that nothing happened when they clicked on whatever it was I told them to make it run.

It turns out she was disabling the macro.

ok......I tell you there's a macro.....and you disable it....then tell me it doesn't work.....

One thing about macros, though, is they are executable files, so malicious things can be done with them. If you know who made the macro, then fine, enable it. But if not ....

I do a lot of macros in Excel. One is just called Basic Format. And that's what it is. It sets a print area, headers and footers, turns it to landscape print mode and makes everything a particular font, plus it adds a filter. At this point, I could not live without it. Every time I run a report, I run Basic Format, it finishes in less than a minute and everything is good to go.

Where's the key to start a Macro in Word (I can look it up tmrw if it's too much trouble for others to check; I'm just lazy right now ... heh)? And in Access (something I've never been able to find)?

jespah, how to you access your macros?

do you use shortcuts keys? I'm too stupid to remember which keys I've assigned.


You know what I know next to nothing about?
Pivot tables....I've read the instructions, and made them, but then I can't seem to figure out what exactly I'm supposed to do with them.

I can't think of a good purpose to put them to either.

Any pivot table people out there who can explain in in small words?

That is FANTASTIC! I don't know how I managed without macros so far! I do indeed type long paragraphs of the same or similar stuff... or have to go hunt for it, then copy and paste it. Can I save a paragraph as a macro? Sort of a forever stashed text? Our mission statement for example?
I also have formatting battles with my computer. It's decided to be more European than me. Everything is A4, margins get all weird, and I have to do page setup, or correct it in printer settings before each time I print. Less I forget and then things get cut off. If I can just have one macro to do that, that would be great.

Or else, change the default settings - which I tried and tried, and he just won't have it. I probably have to change it somewhere else, in control panel or where... anyone knows?

Pivot tables? I'm all ears too!

I know what dag is going to be doing tomorrow.

dagmarka, the mistress of macros.

Glad I'm not the only one getting something from this thread. Practice a few. Once you hit "record"; it records everything you do. You can even open another program and extract some data from it as part of the Macro. Just remember that even though it's an easy way to program a computer; it's still going to do EXACTLY what you tell it to do. As a rule; I save a new copy of whatever I'm working with in case I screw it up (usually do)… kinda like having a restore point.

I generally just record macros separately so that I don't send 'em along to anyone else, then I just open up that file every AM and go to Tools>Macro>Macros et voila.

I wrote, for work, The Wonderful World of Pivot Tables (really, I swear). I'll see what I can adapt for here, this afternoon or later this week, time depending.

Try Start<control><regional> select your region

Then in microsoft word <Tools> <options><general> you will find an option with a drop down box for "measurement units" select the one you want.

Tools is also the place to look for <macros><record>.

The Wonderful World of Pivot Tables (abridged)
Let me know if any of this is unclear ---

Note
Always use a fake spreadsheet that you don't care about in order to practice. This can be a tricky maneuver and there are a lot of steps so it can be easy to forget how you got somewhere.

Premise
Let's say you have a boatload of data. And it's got various fields. Like:

• Name
  
• Street Address
  
• City
  
• State
  
• ZIP
  
• Phone
  
• Gender
  
• Marital Status

Selecting a base of data
You need to choose the data that you want to report on. Usually, you want to report on the entire spreadsheet, but on occasion you might want to report on smaller chunks of it.

Naming a range
The best way to assure that you always report on only the areas you want to report on, is to name a range. Select only the data in the spreadsheet that is germane to your report. Let's say you want to count how many people live in Massachusetts. In that case, you would capture just name and state. To really play with the spreadsheet, you might want to capture everything and then you can try out the various options.

• Select all of the data you want, nothing more, nothing less. If you want nonadjacent cels, use the ctrl key.
  
• Select the Insert drop-down menu at the top.
  
• Select the Name drop-down menu.
  
• Select Define.
  
• You'll get a little popup menu that says "Names in workbook:". Just below that there is a space to data enter. Type the word database. Click OK.
  
• In the upper left corner, there is a pull-down, it's located just below the File and Edit drop-down menus. Pull down on the arrow to the right and you should see the word database as an available option. If you select it, you'll see that the range you just defined is now selected.
  
• If you add or delete rows, columns or records within the range, the range will grow or shrink accordingly. If you're unsure of this, try adding a row (which you can always delete or just not save the file after you've done so) and then re-select the range by pulling on the drop-down. You'll see that the range is one row larger.
  
• If you ever need to redefine the range, just follow steps 1 - 5 again.

Note: you can define a smaller range if you wish to only report on a few columns or rows. You could also define a larger range, but you'd end up with rows and/or columns without any data.

Making the initial pivot table

• Once you've named the range, go to the Data drop-down menu and select "PivotTable and PivotChart Report …". A small menu will pop up and it will say, "Step 1 of 3". You probably will want to go with the defaults for starters, which are "Microsoft Excel list or database" and "Pivot Table". Click Next.
  
• The program will automatically default to selecting the range named database. If you named the range something else, just type it in where the program says database. I don't like to hit browse as that leads you to selecting another file. Click Next.
  
• The default selection is "New Worksheet". You should pretty much always select that, as selecting "Existing Worksheet" means you can end up with your pivot table overwriting your base of data. Click Finish.

Playing with fields
You'll get a new page, with two menus (one says "Pivot Table Field List" and the other one just says "Pivot Table") and a sheet with grey writing on it.



Literally, all you need to do is, grab a field from the pivot table list and drag it into one of the grey areas.

• Drop page fields here - if you drop a field here, you'll get a little pull-down for each unique value in that field, including blanks. Pull down and you'll get a small menu showing all of the options available. Land on one and click OK, and you'll get a pivot table just showing the data that applies to that particular value for that particular field. I have used this field to capture data for particular states and for all states.
  
• Drop column fields here - if you drop a fields here, you'll get columns for each of the values available, including blanks. Be careful with this, as you will get an error if you try to place a column with a lot of variables in here (what's a lot? More than 256 unique values). In our example, marital status would be a good field for this type of treatment because it doesn't have a lot of options (married, single, divorced, separated or widowed).
  
• Drop row fields here - if you drop a field here, you'll get a de-duped list of all of the values, in alphanumeric order. This is very useful for getting unique values for a particular column. You can put the field with the most variable values in here, as the limit is, in theory, over 65,000 unique values (in practice, though, you'd create an unwieldy pivot table that would take a long time to recalculate, so that many unique values just aren't practical). City might be a good field for this kind of treatment.
  
• Drop data items here - if you drop a field here, a calculation will be performed on it. For text fields, it will count all non-null records in that column. If you right-click on the setting (where it says "Count of …"), and then click field settings, if the field you have placed in there can be subject to some other type of calculation (say, it's a straight numeric field), you can select another type of calculation, e. g. sum or average. This little menu also contains some numeric formatting options, and you can change the name to something more meaningful, if you wish. This is where you show the relationships among different data fields. If you just put one field in here, you'll get an aggregating calculation (it will default to count, but you can change it to sum, average, etc. if the field is of the straight numeric type).

The Relationships among Types of Data
Here's where it gets fun. Conclusions can be drawn, based on what you put where, and which calculation(s), if any, you perform.

Try this example:

1. Place the States field where you put row fields.
   
2. Place the Marital Status field where you put column fields
   
3. Place Name field in the data field
   
4. What you end up with is a count of the number of persons with each separate marital status in each city. It's a count of the number of persons who meet both conditions, such as a divorced person in Illinois. If no one fits a particular combination, there's just a blank in that cel. For data fields which could have more than one type of calculation, if you click where it says "Count of …" and select field settings, you can change the calculation to sum, average, etc.

Finesse and Troubleshooting

• To get rid of a field in the pivot table, just drag it out. You'll see the cursor become a black cross with arrow heads. Once you've pulled the field out, you'll get a maroon-colored x. Then just release the mouse button.
  
• To move fields around, just click and drag. The cursor will change to a blue rectangle as you move the data field into a different area of the pivot table. The just release the mouse button.
  
• To make a second pivot table, if you are using the same base of data, just right-click on the spreadsheet's tab (all the way at the bottom, where it says Sheet1, Sheet2, etc.) and select Move or Copy. Then decide where you want the new sheet to go and check Create a Copy, then click OK. This second pivot table will be based on the same base of data as the first one.
  
• To make a second pivot table, based on a different cut of data, you should first name a new range (see Naming a Range, above). If you're going to use more than one range, it pays to give the ranges meaningful names, e. g. FirstTwoRows or whatever. Underscores are fine for range names, but spaces and numbers are not. Try not to make the names too long. Then you can create the pivot table as above, but be sure to type in the correct range name in step 2.
  
• You can put more than one field into a column or row of a pivot table, but you will end up with subtotals, which you might not want. If you rearrange the order of the fields in the pivot table column or row, you'll get different subtotals. You might do better using the page and row options together.
  
• Don't put more than two fields in a column or row. Pivot tables are meant for midlevel analysis but not for very sophisticated aggregating. You'll do better using MS Access for those types of data slices.
  
• Know your data. It pays to look at the base of data before you create a pivot table, to determine the best and most meaningful way to present the data.
  
• If you get stuck with a pivot table you don't like or don't understand, remove the fields and start again.
  
• For row fields, if you pull down on the tab, you can select which values you want to see. This way, e. g. you can hide any blank values. To restore all values, just select "(Show All) at the top. You can also do this with column fields, but not with data or page fields.
  
• If you "lose" the pivot table list menu, don't panic. Just click anywhere on the pivot table itself and it'll come back.
  
• If you click on the other little menu (the one that just says "Pivot table"), you have formatting options if you click the icon with the lightning bolt. The basic, non-fancy formatting is all the way at the bottom.
  
• You can create a chart (it's automatically placed on a new sheet) by clicking the chart icon (it looks like a little bar chart) on the smaller "Pivot table" menu. If you change the pivot table, you'll change the chart it's based on automatically.
  
• If you change the data in the base, make sure to refresh the pivot table(s) it is based on. You can do this by either clicking the exclamation point on the pivot table menu (the smaller one) or by right-clicking the pivot table itself and selecting "Refresh Data".
  
• If you want to change the range you're using to make the pivot table (e. g. go with a different named range, rather than simply changing the named range), right-click the pivot table and select Pivot Table Wizard. Click "Back" once and you'll be at the point where you added the word database to making the initial pivot table. You can then just type in another range name.
  
• Pivot tables are on spreadsheets and so should be formatted like anything else, with headers and footers, page-centering, sheet names, etc. You might also want to format the sheet tabs, with names and possibly tab colors, in order to make the pivot table really stand out.
  
• While it is possible to remove a pivot table's tab from the spreadsheet where the data is based, that's not recommended as it creates a linkage between the two Excel workbooks and takes up a lot more memory, plus updating the base of data could be done without refreshing the pivot table, so results could become misleading. It's better to keep it all together.
  
• Pivot tables take up more memory than regular spreadsheets, so only use them when you need to.
  
• Finally, if you find yourself continually bumping up against the limitations of pivot tables, and wish they could do more, you're ready for MS Access. But that's a training document for another day.

jespah, I'll read this tomorrow, and fool around with it.

cut and paste operation
Not to interrupt, the flow of the threads, but ...

Hopefully this isn't common knowledge ... and hopefully no one wrote about this before.

Cut and paste of a small to a large block of text can also be done using 'Ctrl C' (cut) and 'Ctrl V' (paste).

For example, if I want to duplicate a block of text anywhere on any application on computer (even in chat program window), do the following:

1. Highlight and select the desired range of text with the mouse, simultaneously press 'Ctrl C' or, if you want the entire range of text currently visible in an active window, simultaneously press 'Ctrl' A. (captures all)
Hint: To deselect text when unwanted text is chosen, just click the mouse once in the window. Text will no longer be highlighted. Reselect the correct range of text again by repeating prior steps.

2. Place cursor in the application or window where you want to insert the range of text currently in your paste buffer, and, simultaneously press 'Ctrl' V (paste).

3. You've now succeeded in inserting the range of text from the paste buffer in your application or active window.

Jesp, you're way beyond me.

Ragman: Good stuff. I didn't know about Ctrl A. On that same line:
Ctrl+Insert= Cut
Shift+Insert= Paste
as well.

Oh, my.

A few more shortcuts (BTW, if you're using the mouse for everything, that means you do a lot of hunting. And you're setting yourself up for a case of carpal tunnel syndrome. Try using keyboard shortcuts instead and save your mouse hand):
Ctrl-A = all
Ctrl-B = italic text
Ctrl-C = copy
Ctrl-D -- in Excel, this means Copy Down; you will copy whatever is in a cel to just below it
Ctrl-E -- doesn't seem to have a meaning in Excel; does it?
Ctrl-F = find
Ctrl-G -- in Excel, this means Go to; you can go to a particular destination cel
Ctrl-H = find and replace
Ctrl-I = italic text
Ctrl-J-- doesn't seem to have a meaning in Excel; does it?
Ctrl-K -- in Excel, this means insert Hyperlink
Ctrl-L -- in Excel, this means make list
Ctrl-M -- doesn't seem to have a meaning in Excel; does it?
Ctrl-N = new; usually this gives you a new file or page
Ctrl-O = open
Ctrl-P = print
Ctrl-Q -- doesn't seem to have a meaning in Excel; does it?
Ctrl-R in Excel, this means Copy Right; you will copy whatever is in a cel to just to the right it
Ctrl-S = save
Ctrl-T -- doesn't seem to have a meaning in Excel; does it?
Ctrl-U = underlined text
Ctrl-V = paste
Ctrl-W = close the current page (can't recall if this closes all of the open files in a particular application)
Ctrl-X = cut
Ctrl-Y -- doesn't seem to have a meaning in Excel; does it?
Ctrl-Z = undo

Chai, send me a PM if anything's wacky, maybe we'll continue this with email if you want to push files back and forth.

Small correction, Jespah, it's bold text.