# Wanted: How YOU use Excel!



## Greg Truby (Dec 9, 2009)

Folks, yours truly is gonna start giving Excel seminars around the country. In order to get folks to perk up in their seats, I would like to create examples that strike close to home with as many people as possible. I would be much obliged for anyone that could help me generate these. If you could give information along these lines - that'd be just swell:

*Your industry - for example*
manufacturing
service
education
government
non-profit
tourism
so on and so forth

*Just a general description of where within the industry*
Plastics Manufacturing, or
Electronics Manufacturing
Service: Restaurants
Service: Health Care
Govt: Dept. of Motor Vehicles
Govt: Ministry of Silly Walks
Education: Elementary School
Education: University
you get the picture

*How you use Excel*
Creating pro forma invoices for export bids
Reconciling Customer Returns
Tracking Sales of Various Mixed Drinks
Scheduling for Nurse's Station
Minimum Wait Times for License Renewals
Applications for Grants to Create New Silly Walks
Template for Science Fair Projects
Room Assignments in Dormitories

Again, much obliged fer any help y'all kin lend in this direction.


----------



## sanrv1f (Dec 9, 2009)

Hi Greg,

Let me try to explain something that I was doing for 3 years, the first job where I got the chance to work with excel was the General insurance industry. I was part of the finance team and responsible for preparing the monthly schedules for business results.

It was data, data and loads of data every day, there were three major parts of it. The Premium, Claims and the Commission, and each of them are from different systems. My job was to consolidate all data into one place, inter-relate them based on few fields and make a large data base. once it is ready, I had to slice the data based on various parameters, like Product categories, Office location, Business partners, etc,

In this job, I made many excel sheets that, when looking at them now, being examples of how to design bad excel sheets, 

If this helps you in any way, I will post about my next job in the Telecom industry, if you need any sample files, PM me your mail-id or mail me (see my signature)


----------



## Greg Truby (Dec 9, 2009)

Thank you, Sankar.  But no, I do NOT want anyone's sample files.  I'll just make stuff up that should sort of approach what you describe.  I'll do it that way for a couple of reasons: #1 - I need to be teaching a specific concept, so I'll design the workbook to fit whatever concept I'm focused on in that part of the day and #2 - I would never, ever want to disclose any proprietary information.  If I don't *have* it, then I cannot disclose it.  However I do thank you for both the offer and for your example application of Excel.


----------



## Norie (Dec 9, 2009)

Greg

I once used it to solve crossword-type puzzles - does that count.

Seriously though I've mainly used it for data analysis in finance/insurance.

One major project involved millions of records, which I manipulated in Access, but the main analysis, ie the number crunching, was done in Excel.

Oh, and I made some pretty graphs too for the nice men from the auditors.


----------



## TinaP (Dec 9, 2009)

I'm the applications specialist in a bank.  One of the basic things I've used to show the power of Excel is amortization tables.  Another project (which could apply to any industry) was taking a pile of raw data and massaging it to provide a profit/loss comparison report for each of our branches.  

Of course, a Sudoku solver would probably get people to sit up and notice.


----------



## Brian from Maui (Dec 9, 2009)

Aloha Greg,

I've got a vacation tracker, projecting labor cost, weight and balance programs for 767 and 717 aircraft, automatic work scheduler, and some other office stuff.

Get your behind here and I'll give them to you!  

NAH, just give me a shout if you're interested!


----------



## meguilbert (Dec 9, 2009)

Hello Greg,

    I use excel in poultry breeding. We use it to track and analyze QA data,trends at hatcheries and farms. All of which are used for problem solving and to improve our processes. We also use it to track training and create forms.

HTH,
Matt


----------



## Norie (Dec 9, 2009)

Tina

There is a Sudoku solver out there - I think it was Andy Pope that did it.

But it wasn't perfect.

Mind you, it was good enough for me to stop wasting my time with my lame attempts to create one.


----------



## TinaP (Dec 9, 2009)

Oh yes, I know about the Sudoku solvers.  I just think it's a good way to showcase some of what Excel can do--especially for beginners.


----------



## Norie (Dec 9, 2009)

Tina

I don't think it is really.

You could probably write code for that sort of thing in any computer language you like.

I don't thing such a thing really demonstrates anything about Excel, but it could probably be a good lesson in logic/programming.

And that sort of thing could be applied across the board.


----------



## TinaP (Dec 9, 2009)

True.  But my point is, beginners to Excel don't fully understand the potential.  At least once a week I come across people who use Excel daily, but can't write a formula.  Many times, they put numbers in boxes then turn to their adding machines to calculate, then put the answer to their calculations in another box.  Seeing how powerful Excel is goes a lot further than having someone say, "Spreadsheets are powerful tools used to analyze data."  And showing them with a puzzle that they are familiar with can go a long way.

Sorry for hijacking the thread.


----------



## sous2817 (Dec 9, 2009)

TinaP said:


> Many times, they put numbers in boxes then turn to their adding machines to calculate



I spend the bulk of my days stopping the above.  Also I help automate manual entry, compiling the data into something more "higher level", and showing my various managers that it isn't as bad as their bosses tell them it is (pretty graphs help!).

For what it's worth, I work in the healthcare industry in an area that's very metrics driven...


----------



## Greg Truby (Dec 9, 2009)

Sous,

Thanks, can you give me some examples of some metrics that your roll up using Excel?  Or what some of the manual entry processes are?

Tina - can you give a couple of specific examples?

Matt - thanks.  I actually am familiar with the swine production sector, so I can totally relate to how you'd use it in poultry.


----------



## Norie (Dec 9, 2009)

Tina

I really don't think that 1000+ lines of code, which is what is in the Sudoku solver/creator/whatever I'm referring to is a good start point for an Excel beginner.


----------



## TinaP (Dec 9, 2009)

I had one person ask me to set up a table to track cash advances for each branch. I had her draw how she wanted the table to appear, which took her about 5 minutes as it was very basic and then we set about creating the table (borders, etc.). Then she started filling in the numbers for each branch and calculating on her adding machine. I can't remember the particulars, but she added two columns together, then subtracted the third column and placed the result in the fourth column. 

I asked why she used the adding machine when she could use Excel, and I still remember the conversation:
"Why don't you just use Excel?"
"Because I don't know how to do it that way."
"I'm standing right here; why don't you ask me?"
"OK. Tina can you show me how to do this in Excel."

When I showed her how easy it was to write a (simple) formula. She looked at me and said, "That was easy." I couldn't deny it, and told her to call me any time she wanted to calculate something since I really didn't think she would remember it.

PM me if you would like copies of some of the generic files I've created.


----------



## TinaP (Dec 9, 2009)

Norie,

You're right, it's not a good place for a beginner.  I was thinking of the Sudoku Helper template that you can download from Office Online.  Everything is calculated via formulas.  It's certainly not flawless--I've found a number of (mostly cosmetic) problems, but I don't think I found any problems with the logic.  

I challenged myself to improve upon it without using VBA and it's taught me a great deal about formulas, conditional formatting, etc.


----------



## sous2817 (Dec 9, 2009)

Sure...just to keep it somewhat blinded here is what people do here:

We're in the business of helping smaller business work as distribution points for a larger business. There's a lot of red tape involved so we work with the small business to get everything filed away. The large business wants to know how long it takes each small business to get all of their red tape completed and approved. So data entry is something like:Excel WorkbookABCDE1EmpBig Business NameSmall Business NameStart DateEnd Date21Data1XXX1/1/20093/6/200934Data1YYY1/13/20093/3/200941Data1ZZZ3/12/20094/19/200954Data2AAA3/15/20096/6/200962Data2BBB3/25/20096/22/200971Data3CCC3/30/20095/5/200984Data1DDD4/12/20096/21/200991Data2XXX4/20/20095/29/2009103Data3YYY5/1/20097/28/200911etcetcetcetcetcSheet1*Excel 2007*

This is all put in manually as each small business process is started. Each employee wants to know if they've met their individual performance goal for bonuses / raises (# small business brought up, median times, etc). Their managers want to know how many each employee got up by month, quarter, year, their median times all by office number, etc. The managers' manager want to know overall how the department is doing (# up, median times, etc), and each big business wants to know how their individual project is doing, whether it's meeting timelines, deadlines, etc.

Before I was here, some poor soul was doing a lot of filtering / copying / and pasting to get each employee / office / big business in their own sheets and then calculating the metrics. The whole thing would break down if a manager's manager wanted to know how office 1 and 3 compared to office 2. The process was done once at the end of the year and would take about a month to sort through it all.

So using a few formulas / pivot tables / splash of VBA I'm able to split all this out in to different sheets so each entity can get their information when


----------



## SydneyGeek (Dec 9, 2009)

My most recent Excel jobs have been:

1. Create 5-year forecasts for a large real estate developer. Each sheet was a different project; data was loaded to Access for consolidation, then pulled down to reporting workbooks (State and National)

2. Reporting workbooks using an OLAP system as the backend data source. Apart from upload and download, users could double-click a company name to hide or show detail for subsidiaries. 

3. Pivot tables using an external database as the data source (Monthly reports). Users were able to view only their State's data. Updates were loaded from a third system each morning, and required extensive reformatting and 15-20 calculated columns before it could be used in the pivots. All the reformatting was driven using VBA.

Denis


----------



## milkman (Dec 10, 2009)

I use excel to run production for a milk plant that produces 200,000 gallons of product a day.

I first created a weekly production program in excel that looks at Issues of each product (about 115 items)from previous week, subtracts inventory and tells me what I need to produce that day. I then created a daily workbook (template) that has twelve filler machine run scheduled and product code date, I pull what I need from weekly into daily workbook and print operators schedule report and they use it to produce product. Takes about an hour a day to run this.

 I have worked at plant for 10 years and can have inventory down to 100 units or less on production of 30,000 before next run starts giving our customers the freshest product on the market. Not to mention the cooler space that is free and labor cost of transferring over stock product.


----------



## snowblizz (Dec 10, 2009)

Want them to perk up? Spreadsheet horror stories! 

"If you don't want to loose millions or billions like these guys, then listen up!"

If you can influence people to check their spreadsheets a little more thoroughly you'll probably have done a lot of good.


----------



## Domski (Dec 10, 2009)

90% of the time I work in local government as Principal MI analyst for the shared services at Leeds City Council. This mainly involves dragging data extracts from the SAP HR system and using the wonders of Excel to crunch the data. For one off requests this will mainly be done manually, if it's going to be a regular information requirement I will automate the process using VBA.

The other 10% of the time I seem to spend helping other folk to do their jobs better (hopefully) either by teaching them how to do things with functions or putting together little Excel apps where they can just click the buttons and it does it all for them.

Dom


----------



## gardnertoo (Dec 10, 2009)

I'm a technician at a power generation facility owned by a utility.  The control system used to run the facility has a data historian to which I have access using Excel.  I can pull the stored values of some 3000 instruments around the plant, at intervals as fast as 1/10 of a second for some instruments, covering any time period back to early 2004 when the plant first started.  I typically use this to look at only the most recent data, say a month or less.  Sometimes I am trying to compare how a given parameter acted last year at this time, or comparing it's behavior before/after a repair, or how parameter "X" acted when parameter "Y" was within a specified range, etc.  It's a great troubleshooting tool.  This is usually done with fairly simple workbooks, such as a straightforward graph of pH vs time.  I even have that rare beast, an appropriate use of the radar plot:  





You're looking at the calculated temperatures of 14 combustors arrayed in a circular pattern, at 10 second intervals.  You can instantly see that combustor #4 is the one which had the problem that shut the plant down, which in turn directs our troubleshooting efforts.  
A somewhat more complex example that I consult once a month or so, and tweak a little each time, is a predictor sheet which estimates the date when our turbine could be expected to reach one of two possible maintenance milestones, based on historic operating patterns.  The two milestones are number of startup/heatup events, and number of running hours.  The data gathered allows for other comparisons, such accumulated values of hours, watts, starts, etc over a year, which I chart for year-to-year comparisons.
The most complex one I've done, which won me an award within the company, was a homemade Maintenance Management System.  This project would probably have been better suited to Access, but I know Excel.  So I forced Excel to do what I needed.  LOTS of macros and userforms.  It had a front-end for the users to enter requests for maintenance, another for the maintenance staff to choose jobs and enter manhours, status, comments, etc, and a back-end that did the scheduling of the routine preventive maintenance.  We ran with that spreadsheet for almost a year while we got the SAP Plant Maintenance Module ready to roll-out.


----------



## Norie (Dec 10, 2009)

gardnertoo

Are you sure you aren't a game developer?

That image looks pretty much like an arcade game I used to play, or well try to play, back in the eighties.


----------



## Jon von der Heyden (Dec 11, 2009)

Greg Truby said:


> Folks, yours truly is gonna start giving Excel seminars around the country. In order to get folks to perk up in their seats, I would like to create examples that strike close to home with as many people as possible. I would be much obliged for anyone that could help me generate these. If you could give information along these lines - that'd be just swell:
> 
> *Your industry - for example*
> manufacturing
> ...


 
*Industry:*

Conglomerate

Industrial (traffic, transport, buildings etc)
Energy (fossil, wind etc...)
Healthcare (diagnostic equipment)
Other (IT solutions)
*Business:*
Division: Global Shared Services
Department: Finance & Business Administration
Customers: Operating Companies
Shared Services are EBIT neutral, focus on savings for SOCS, not profit oriented...


*Typical Models:*

Costing and product pricing (costs broken down to product level, price incl cost + small margin to support investment activity)
Revenue and cost budgets and forecasts. Revenue forecasts at customer detail.
Monthly reporting - budget / forecast vs actuals (P&L, Balance Sheet and Cash Flow), and variance analysis.
Debtor and Creditor Reporting (aged analysis, DSO, DPO etc).
Customer reporting (open book policy so full detail of supporting volumes and charges, for each customer for each product). Budget / forecast vs actuals and volume and price variance analysis.
Provisions models - e.g. employee holiday + bonus provisions.
Compliance Models (SOX compliance) - e.g. payroll monitoring.
Savings models, calculating and tracking savings on corporate initiatives.
Data is sourced from SAP and BI (Business Warehouse) but often stored in tables (Access) along with other data that is not collected in the ERP.

There are a variety of other activities. This is the typical management accounting stuff. But if there is anything here that you are interested in understanding in more detail let me know.


----------



## DonkeyOte (Dec 11, 2009)

Interesting thread - and some interesting uses !

I've often used Excel as the basis for ETL solutions - ie taking data from one supposedly "better" system, analysing/translating said data prior to pushing the data into another supposedly "better" system... and normally on an automated basis (ie firing overnight)

The above probably sounds completely mental but in a classic case a  no. of "feeder" systems actually stored _less_ data than the output system required (think OLAP).

Now as I'm sure is the case in most firms - the executive board (being the board) decided that rather than spending money investing in input systems that actually captured the info they needed it would in fact be far better spent on a nice new and expensive boardroom fit-out ... 
_(presumably so they could be comfortable whilst discussing what to tell the auditors when they asked why all their proprietary systems were from the dark ages and no longer really "fit for purpose")._

End result: "someone" (read: muggins aka "System Red Adair" to my colleagues - hey, they weren't technical!) had to somehow intervene within the existing workflow and add an intermediate step wherein a whole host of as yet "unseen" data could be created from scratch ... the rules behind the creation of this data was I would say relatively complex (code permutations across variety of fields etc).  

To me XL was the most obvious application of choice given the data storage element of the process was minimal (& more importantly temporary) and it was in essence just calculation intensive.   Furthermore the use of XL brought with it other benefits, namely:

a) the system was easily adjusted 
(via a few straightforward (well designed?) reference tables)

b) audit-able 
(well reasonably so if you know my code / formula sledge hammer approach!)

c) familiar tool to colleagues (& lest we forget - the auditors!)
_meaning should I ever have had the audacity to say go on holiday others could in theory have implemented the process on an ad hoc basis without having to phone me at unmentionable o'clock or whilst I was holidaying say on the other side of the planet! (it didn't work as you can tell)_

I have to say that as time wore on I used XL less and less as a primary tool and more and more along the lines of the above - web apps and OLAP systems became the main point of reference for the majority of end-users and XL for me became something of a tool in between but no less valuable given XL was far better at performing ad hoc calculations than either of the above whose strengths lay elsewhere (security & storage respectively - IMO).


----------



## Cbrine (Dec 11, 2009)

Let's see....
Industry:  Health Care/Service

Business
Private Retirement and Long Term Care of the Elderly
Corporate Human Resources

Usage
Annual Merit, Bonus, LTIP, Vacation Analysis,Service Awards,etc...  I use excel to do a lot of process automation, from programatically producing distribution workbooks, to consolidating the completed workbook together agian, intelligent mail merges with word, Parseing data from the crappy Crystal Reprots Server that this company seems to enjoy so much(They make them pretty with breakpoints and summaries, which makes them pretty usless for data analysis until I parse them, and it's easier to build a VBA program than to get the frigging report writer guy to understand what I want  ).  I also do a large amount of salary analysis using excel as well.  I've integrated excel with outlook using redemption to bypass security restrictions, to make it a mass mailer.  I've also done this with STMP bypass outlook all together.  I'm sure I've done some other weird stuff as well


----------



## arkusM (Dec 11, 2009)

*Your industry - for example* 

Energy
*Just a general description of where within the industry* 

Natural Gas producer/marketer
*How you use Excel* 

We use excel to forecast the companies Netback bi-weekly
I use Excel to model our hedging program via Bloomberg real time data feeds
I calculate and verify sales revenue monthly.
Various reports, tracking sheets
We use it to store data (oh the horrors LOL) Excel is simple for my co-workers as most are familiar with it.
Track and calculate pricing spreads for different delivery points
I have used/abused excel to save attachments from our email system (groupwise) and even verify and rename files. Thanks to the help of people online.
Automating web queries where I can.
Automating website logins 
On the side I automated a spreadsheet for the lunch time poker point tracker. The guy that was running the sheet did all the calculations by hand which took an hour. Now he enters the chip count for each person and the sheet shows the rankings of the players and the deltas for the day ie total points and net points. They have developed a scoring system because we don't play for money at the office, don't want to push our luck.


----------



## Annalyzer (Dec 11, 2009)

I work for a private non-profit community action agency.  We use Excel for many things.  Of course, we use it for budgeting which involves a lot of "what-if" type scenarios.

Another interesting way we use Excel is to transfer information between databases.  We operate on many different funding sources, many of whom require use of their proprietary database. Then, when we need to compile information for reporting, we are usually able to export data from one database into Excel, save it as type .csv and import it into another.  With macros, we are often able to do this with little or no user intervention.

We also use Excel to allocate expenses across a multitude of funding sources.  We can set the spreadsheet up at the beginning of the fiscal year according to the budget and then use it all year long to allocate various expenses.


----------



## Gerald Higgins (Dec 13, 2009)

Norie said:


> That image looks pretty much like an arcade game I used to play, or well try to play, back in the eighties.


 
Tempest ?
http://en.wikipedia.org/wiki/Tempest_(arcade_game)

Man, that was an awesome game. Sadly I wasn't awesome at playing it !


----------



## Colin Legg (Dec 13, 2009)

*Your industry*

Finance
*Just a general description of where within the industry* 

Investment Banking
*How you use Excel* 

Primary: playing battleships
Secondary: answering questions on MrExcel
Tertiary: financial modelling


----------



## xld (Dec 14, 2009)

*!*



Colin Legg said:


> *How you use Excel*
> 
> Primary: playing battleships



LOL! At last, an honest man!


----------



## xld (Dec 14, 2009)

TinaP said:


> I'm the applications specialist in a bank.  One of the basic things I've used to show the power of Excel is amortization tables.  Another project (which could apply to any industry) was taking a pile of raw data and massaging it to provide a profit/loss comparison report for each of our branches.
> 
> Of course, a Sudoku solver would probably get people to sit up and notice.



http://www.andypope.info/fun/sudoku.htm


----------



## jasonraedy (Dec 14, 2009)

I work in manufacturing for a very large company making auto parts.  I am considered the excel guru here and use excel extensively to download large amounts of data from either shared drives in pdf format or oracle or sharepoint databases (among others) and make easy to use summaries that allow the user to drill down from top level summaries to mid level summaries and finally to the data.  I also use many graphing macros for summary charts with key data extracted from the fields.  I made literally hundreds of programs and the value of these is priceless.


----------



## CMIIandExcel (Dec 21, 2009)

*Your industry*

Construction(Rail & Major Civil Engineering)
*Just a general description of where within the industry* 

Currently East London Line Project, £500 Million project to referbish and extend a railway.
*How you use Excel

*

Automation of Asset data capture from predfined hiearchy.
Reporting of Documents current and issued, to the client and site.

As a vessel for transfering data into a Configuration Management Database.
Daily and weekly work allocation and tracking.
Staff performance tracking for personal developmnent.
Validation and verification of data recived.
Communicating with engineers who seem only to understand badly writen word documents, and badly designed (on thier part) spreadsheets

Ok so i am a bit bitter with the last point!

regards Mike


----------



## Excelestial (Dec 21, 2009)

I use excel to gauge and forecast national sales of our commercial fiber products.  I've used it extensively in the Dept. of Defense for covert operations I've worked; in a University for research projects; in small private businesses to file state-mandated cost reports, track activity, expenditures, and clients.  I currently design analysis models by automating massive, dynamic and robust spreadsheets that make my work "audience" give serious contemplation that I am the borderline-mad type of "genius" LOL.  Which isn't a bad title when your boss gives you space to accomodate the idiosynchrasies that come with the it. LOL

And so even if you don't splice DNA at the job, or are the administrator of the Dept that builds Nuclear warheads on stealth missiles, being on top of the Excel game gets you hailed as the company's Excel guru.  The application has helped make me quite indispensible as my job was spared during two rounds of layoffs amid the recession...and I was the "new kid" on the block in a job that was created for me.  The ONLY "new kid" spared, no less.  Those that were going out the door, asked if they could keep contact to have readily available access to a valuable resource.  

That being siad, if I received notification of a seminar that would enhance my skills, I would be attracted to any existing opportunity of making myself highly marketable and retention preferrable.  Job security is the new American Dream.


----------



## ge0rge (Dec 21, 2009)

I use excel for automating tasks related to Quality Control and Engineering (prestress members). I have lately tied excel to a concrete batching system to perform data analysis for tolerance tracking.


----------



## scottylad2 (Dec 21, 2009)

Brain Training! I use Excel for various things, i used to deal with staff rotas and timesheets so being able to calculate all the hours people worked and keep track of annual leave was easy via Excel. 

Don't have to do that now, but i do still monitor some of the Rotas and spreadsheets i've made and am continually getting "tweaks" for them from this site. 

Also since being on this site it's been great looking at the posts people put on here and trying to solve them. I work some weird shifts so being in the house for quite a long time on my own, i can either hoover, do housework or do some "brain training" by sitting looking at this site.......I could even work out the probability of what i'm most likely to do now lol!


----------



## T. Valko (Dec 22, 2009)

ge0rge said:


> I use excel for automating tasks related to Quality Control and Engineering (prestress members). I have lately tied excel to a concrete batching system to perform data analysis for tolerance tracking.


I used to work at a cement manufacturing plant. 21 yrs then was down-sized (along with everyone else!).

12 of those years as a quality control lab analyst. We used Excel extensively for various tasks. Mix designs (raw material what-if-analysis), xray fluorescence integration and calibration (mind boggling stuff!), databse, agency reporting, etc, etc.

I then moved on to be a process supervisor where they (we) rarely used Excel until I got involved! Employee scheduling, production forecasting, inventory analysis, etc., etc.

Here's something that is really funny. Borland Turbo C (a popular C compiler in the mid 80's) used to ship with sample source code for a spreadsheet progam. My first experience with a spreadsheet was with this compiled code! We actually used this as a database for a while!


----------



## Sandeep Warrier (Dec 22, 2009)

I started using Excel about less than 3 years back. Mainly started learning tricks to reduce my workload and was then addicted to Excel 

*Industry:*
Due Diligence, KYC, AML

*Where within the industry:*
Support to Due Diligence research team

*Excel usage:*
Tracking the amount of work done by my team. Preparing a few MIS reports and have just progressed to making small tools in Excel that help automate a few tasks.

One of the main points i've come across is ignorance of Excel functions/Excel in general among many people. 

For example, many people I know are 100% certain that the only use of VLOOKUP is to compare 2 columns of data to identify missing elements 
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">


----------



## ge0rge (Dec 22, 2009)

#NAME? said:


> I used to work at a cement manufacturing plant. 21 yrs then was down-sized (along with everyone else!).
> 
> 12 of those years as a quality control lab analyst. We used Excel extensively for various tasks. Mix designs (raw material what-if-analysis), xray fluorescence integration and calibration (mind boggling stuff!), databse, agency reporting, etc, etc.
> 
> ...


 
I automated some tasks related to pre-stressed strand calculations and some believe I made others "dumb" in doing so. Why work harder instead of smarter. I won't deny that I had a great deal of help here in accomplishing alot of tasks but at the same time I did some projects on my own since coming here that i otherwise would have never been able to do. 

I am currently working on a complete Quality Control / Concrete Mix Design excel project at home, untied from work, that I can use for my own personal use. I am always looking for any excel related material for ideas as well.


----------



## PaulGroom (Dec 27, 2009)

Greg Truby said:


> Folks, yours truly is gonna start giving Excel seminars around the country. In order to get folks to perk up in their seats, I would like to create examples that strike close to home with as many people as possible. I would be much obliged for anyone that could help me generate these. If you could give information along these lines - that'd be just swell:
> 
> *Your industry - for example*
> manufacturing
> ...



I would approach this in a slightly different way. 

I would establish who is going to be attending the seminars - Small business users? eBusiness users? Teachers? Families? Individuals for career enhancement? Bespoke seminars (industry specific)? etc etc..

What level is the seminar aimed at - _New user? Novice? Intermmediate? Advanced?_

What are the 'goals' for the seminar - _At the end of this seminar you will know how to......_

You've covered all the bases in your list so with a little bit of thought on the part of the attendee they should be able to see how a dormitory allocation list could just as easily be a staff holiday roster (both involve assigning something to someone).

The danger you face is if everyone on this forum was in the service sector, you create service sector type examples which may have no relevance to your customers. They go away presuming Excel is a service sector programme.

As long as you've covered each feature of Excel that you set out to cover then you have achieved your goal. The actual data/example you use to highlight that feature is not really important.


----------



## Cindy Ellis (Dec 29, 2009)

I'm in the medical device manufacturing industry (USA), currently in Research & Development, but spent some time in Quality Systems.
Other folks in my company use Excel for 

financial modeling
creation of batch record forms (with formulas protected and locked, spreadsheet validated and controlled through our document control system)
creation of various other less "critical" forms

I use Excel to automate huge amounts of data analysis, create user interfaces to otherwise complex information, and help other folks accomplish in a few minutes what they expect will take hours or days
For instance: 

A macro used by our mfg. dept. that called an external program to convert a binary log file into human-readable form (~7200r x 25 c), pull the converted file into Excel, then summarize and analyze it.  This broke when we switched to Excel 2007 a few months ago...but it was used daily for 10 years (pats self on back).
A macro that simplified the process of looking up the countries where a given product was authorized for sale (appropriate regulatory clearances, etc.).  This macro also created and saved a formatted Word report from within the Excel macro.
A macro that opened ~ 6000 large (3600r x 85c) data files in various directories and subdirectories (searching recursively using a routine from the Mr. Excel site...maybe Nate O?), extracted the bits of data I needed, identified the data with the directory and filename, appended the info to a summary file, closed the file, and went on to the next.  I was able to complete the analysis of all 6000 files in about 4 hours (just over 2 seconds per file)...therefore making me a hero in my manager's eyes
A macro that helps our Field Service Engineers, by creating a summary report and Pareto chart of the problems recorded internally by a given system since installation.

Have fun developing your seminar!

Cindy


----------



## Greg Truby (Dec 29, 2009)

PaulGroom said:


> ...I would establish who is going to be attending the seminars - Small business users? eBusiness users? Teachers? Families? Individuals for career enhancement? Bespoke seminars (industry specific)? etc etc..


All of the above. Unless it's an on-site where a company has brought in a trainer just for Excel, these are "general public" so it'll cover the gamut of industries, hence the desire to create examples based on a wide variety of scenarios.



PaulGroom said:


> ...What level is the seminar aimed at - New user? Novice? Intermmediate? Advanced?


Beginners to intermediate.


PaulGroom said:


> ...What are the 'goals' for the seminar - At the end of this seminar you will know how to...


That's already established by the brochures the customers responded to. The course content must cover all of the points in the brochure - there is a workbook that essentially guarantees this gets done.


PaulGroom said:


> ...As long as you've covered each feature of Excel that you set out to cover then you have achieved your goal. The actual data/example you use to highlight that feature is not really important.


Ah, but it is in the sense that you must weave a story the audience can connect with. I need fun and interesting stories [Bill's a master at this BTW] that the audience can identify with in order to keep the audiences engaged. For the best connection, I need realistic stories; and the good folks here have given me a lot of terrific material to work with. So a big *THANKS* to everyone that has, or will respond.


----------



## Darren Bartrup (Dec 31, 2009)

I work for Essex County Council and I think my main task is to create the god workbook.... throw any old rubbish at it and it will know what to do and spit out a shining example of brilliant logic and flow.

As a sideline I either create workbooks which analyse workbooks created by other people, so there's usually a fair amount of tidying up first:
e.g. checking dates of births in the future, dates of birth more than 120 years ago, correcting dates that have been entered in a format not recognised by Excel, cells with text instead of the expecting numeric value.  
I'm sure you've all come across similar problems when looking at a workbook not designed by you.

In the workbooks I build I try and stop any of the above mistakes from being possible.

Apart from stopping errors, my work seems to be filled with counting, averaging, finding maximums & minimums and usually on data that will change from month to month so dynamic ranges are a must.

Charts!  I build lots of charts to because the management like green lines and hate red lines.  So I put in lots of sliders, buzzers, bells and twiddly things - lot of thanks to Jon Peltier and Andy Pope for charting hints.

Then there's the VBA side of it... I won't go into what I'm asked to do with that; I swear it's not natural sometimes.


----------



## daniels012 (Dec 31, 2009)

We are a construction company, our company puts down flooring.
I started out when Quattro Pro was THE top spreadsheet and when Excel became the best I made the move.  Ever since, I have created every form, sheet, report in our family owned business using Excel.  All kinds of stuff like: Brochures, memos, expense reports, inventory, customer proposal sheets, estimating sheets, job folder cover sheets, post cards, sales reports, sales graphs, financial reports.

Many people have said "you should use Word for that" but Excel is so ingrained in my brain, it is just easier for me.

In my personal life, I have created a fantasy football draft sheet.  With the Fantasy, I have created a form to pull data from the internet to track player statistics.
For our little league I created a draft sheet to draft players and then print the data brought in from our leagues online database.


Hope this helps!
As many times as you have helped me on Mr Excel, let me know if I can be of more assistance to you!

Michael Daniels


----------



## Norie (Dec 31, 2009)

Michael

You should use Word for that sort of thing I think - it's sort of designed for it.

There's also another application called I believe Access which can come in handy.


----------

