Most Popular Excel Features -- A Call to Participate

[SkipVought]

This is an impromptu survey. Please copy this format and 'fill in the blanks' if you care to participate.

[tt]
=========================================================================
Industry:

Functional Area:

Excel Version:

My Function:

My Favorite Buttons:

Features I use in support of my users (that many of my users do not use):

Other features that many of my users use:

Additional Comments:

=========================================================================
[/tt]


Here goes...
[tt]
=========================================================================
Industry: Aerospace

Functional Area: Production Control & Logistice

Excel Version: 2007

My Function:
Data acquisition, analysis, reporting
Create work aid tools
Excel training & support

My Favorite Buttons:
Select Current Region
Create from Selection
AutoFilter / Clear (AutoFilter)
Calculate Sheet
New Database Query
Edit Query
Visual Basic (alt+F11)
Camera
Find (ctr+F)
Number Format
Document Location

Features I use in support of my users (that many of my users do not use):
Microsoft Query
Table (ctr+T) & Structured References
ActiveX COntrols / Form COntrols
VBA
ActiveX Data Objects
Named Ranges


Other features that many of my users use:
PivotTable
Chart

Additional Comments:
I create interactive work aid tools, database UDFs for ad hoc reporting to support our users in PC & L, IE, Manuf
=========================================================================
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[N1GHTEYES]

Industry: Aerospace

Functional Area: Electro-Optical Sensor Systems

Excel Version: 2003

My Function:
General consultative,
Feasibility studies,
Innovation,
Project Support

My Favorite Buttons:
Paste format,
chart,
calculate (F9),
Trace Dependents / Precendents,
Solver (menu item),
change reference style (F4)
Insert/Name/Create (from menu)

Features I use in support of my users (that many of my users do not use):
Dynamic named ranges (including use in dynamic charts),
VBA in general (macros / UDFs)
Array formulae
Analysis Toolpak - VBA
Evaluate formula

Other features that many of my users use:
Chart
My own Addin "AL_XL_tools"
Analysis Toolpak
Autofilter

Additional Comments:
I tend not to have "users" per se, because my job is not to provide code / tools, but I often find I make them for my own use and people see them and want them.

I mostly use Excel to:
be an extended version of the calculator showing the intermediate stages
provide simple to moderately complex models showing complex relationships between variables
as an easy means to give applications to my colleagues, which gets round our security restrictions (e.g. no exes, no admin rights to install stuff etc)
display and analyse data
as a knife & fork tool to work up suitable analysis processes on datasets (mainly image data) before coding them into a full VB app.

Is that what you're after Skip?

Tony

 

[judgeh59]

Industry: Electronics Manufacturing

Functional Area: Production Support

Excel Version: 2007

My Function: Use Excel to assist in manufacturing. SPC, Automate certain functions

My Favorite Buttons:
Win-L - Lock Computer
Alt-F11
Cntl-C
Cntl-V
Cntl-Z - UNDO - wahoo!!!
Shift F8 in VBA

Features I use in support of my users (that many of my users do not use):
VBA
VLOOKUP
LISTS
PivotTables
Pivot charts

Other features that many of my users use:
standard excel stuff. Most users here are basic or maybe intermediate users.

Additional Comments:
A lot of what I do is going around and looking at what can be automated using either excel or access.


Be Alert, America needs more lerts

 

[GlennUK]

Industry: various - Banking at the moment ( first time! )

Functional Area: Migration Engineer ( 2002&2003 to 2007 )

Excel Version: 2007

My Function: troubleshoot Excel migration problems, create tools to assist in migration

My Favorite Buttons: Group/Ungroup , Edit Links , Paste Values , Zoom In/Out , Chart Elements drop-down, Close All, Camera tool ... keys= Ctrl-Shift-F3 - create names from selection, and Ctrl-underscore to clear borders

Features I use in support of my users (that many of my users do not use): Array formulas, VBA, PivotTables

Other features that many of my users use: charting, and lot's of it

Additional Comments: I don't have regular users, it's just anyone who has trouble with migration issues, and once they are fixed I shouldn't hear from them again ( yeah, right ).

I use Excel for lots of personal things ... anything you can think of, sometimes just to see if it can be done, and doing experiments to learn new things in Excel.



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[dhookom]

[tt]
=========================================================================
Industry: [blue]Food Manufacturing[/blue]

Functional Area:[blue]Systems Analyst/Programmer[/blue]

Excel Version:[blue]2007[/blue]

My Function:[blue]App development, SQL database admin, Data Reporting[/blue]

My Favorite Buttons:[blue]Buttons I add to sheets or user forms[/blue]

Features I use in support of my users (that many of my users do not use):
[blue]I do a lot of connecting to multiple SQL databases to render data in tables, pivots, charts, etc. Some files have user forms with multi-select list boxes, option groups, combo boxes much like Access form interface. [/blue]

Other features that many of my users use:
[blue]Pivot tables are catching on at my company.[/blue]

Additional Comments:
[blue]Typically Excel is used to store data. I prefer to store data in a SQL database and then use Excel, intranet, or other front-ends to render and format the data.[/blue]
=========================================================================
[/tt]


Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

...anything you can think of, sometimes just to see if it can be done, and doing experiments to learn new things in Excel

My kind o' guy! ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GlennUK]

Ha ha, thanks Skip ... although I'm sure that some people will think it's a bit geeky. :-D

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[MCuthill]

Industry: Financial Institution
Functional Area: Finance Department
Excel Version: 2003
My Function: Report Development & Analysis

My Favorite Buttons:
Ctrl+1 (Format Cell)
Ctrl+X, Ctrl+C & Ctrl+V
Ctrl+Arrows or Ctrl+Shift+Arrows
(I don't use the buttons unless necessary)

Features I use in support of my users (that many of my users do not use):
VLOOKUP(), SUMIF(), COUNTIF()

Other features that many of my users use:
DSUM(), SUMPRODUCT()

Additional Comments:
Our department is of the most skilled Excel users I have had the good fortune to work with. Highly receptive to new functionality/methodologies to gain efficiency.
Of my more favourite tasks is developing Crystal Report Extracts which Excel then references and does further analysis on/bridges in information not available to Crystal Reports.


Aside:
Glenn: I for one do not think that geeky in the slightest. I'd be afraid to calculate how many "off-work hours" I have spent "playing" around in Excel for new ways of doing things or to simply see if it can be done!


Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[SkipVought]

Of my more favourite tasks is developing Crystal Report Extracts which Excel then references

Mike, have you considered doing the data extracts that are now performed in CR, and exported to Excel, directly in Excel?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MCuthill]

@Skip,

*eyebrow raised in curiosity*

Do you mean doing the entire analysis in Excel itself?
The problem is the sheer volume of records I am often dealing with (MS Excel 2003 being limited on # of rows). My dataset is in excess of 350,000 records on most reports and well over 1M records on a few others.

Or have I misunderstood? In which case, I am certainly intrigued...

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[SkipVought]

Well 97-2003 is limited by rows, but it STILL does not preclude doing it ALL in Excel!

I can analyze MILLIONS of rows in a query and return a reasonable resultset to a sheet, such as the size you are receiving from CR, ALL IN ONE PROCESS.

Sure beats 1) running someting in another application, 2) exporting the results and then 3) importing into Excel. YUK! Hit one button, go get a cup of coffee and bask in the effort-free results!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Lilliabeth]

Industry: Database design and custom programming, software training
Functional Area: Operations
Excel Version: 2010
My Function: Oversee ops and Access or Excel projects, also software trainer

My Favorite Buttons: Pivot table, Slicer, Screenshot, everything in the Defined Names group, Trace precedents, Format as Table, Conditional formatting =MOD(ROW(),2)=0 for lookup tables.

Features I use in support of my users (that many of my users do not use): INDEX() and MATCH()

Other features that many of my users use: Insert Function tool

Additional Comments: Powerpivot is neat. I like Solver and Scenarios, but have to make up reasons to use them

--Lilliabeth

 

[SkipVought]

Powerpivot is neat.

Can you elaborate? Is this an Add-on?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MCuthill]

Hey Skip,

Would be slick in one process. But at least I have eliminated the "import" into Excel step - everything runs on dynamic links. Simply click the old "Update Links" (as the files are save in folders by Month) function and ta-da, all done!

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[SkipVought]

But you STILL have to maintain 2 separate applications.

At least you understand that it is a possibility within Excel.

I grant that there may be some instances where the pre-processing can only be performed or can be performed better in another application.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kjv1611]

Skip,

I'm putting my list together at the moment, but I have to ask about your buttons? Are are they all built-in? I don't see them on the ribbon. I suppose you have to customize, and add from there? Either that or I've just overlooked them the whole time.

Skip's favorite buttons:
Select Current Region - same as <Ctrl>+<*>, I assume? I haven't seen the button that I can remember
Create from Selection - Can't say I've seen this one either
AutoFilter / Clear (AutoFilter) I do use this one all the time
Calculate Sheet Haven't seen
New Database Query Haven't seen
Edit Query Haven't seen
Visual Basic (alt+F11)
Camera Haven't seen - you're pulling our legs with this one, aren't you?
Find (ctr+F)
Number Format
Document Location Sounds familiar, but I don't think I've seen

Maybe for some of these, you really meant a menu, button, or shortcut?

 

[kjv1611]

Well... maybe some of those things are by default in '07, but not in 2010? I don't recall right now, but I'll have to look from an '07 machine later.. I'm using 2010 currently.

 

[SkipVought]

Most are not buttons that appear in the default ribbon.

Customize your Quick Access Toolbar, Select ALL COMMANDS from the Drop Down in Excel Options and then scroll to the names as listed for each button.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Camera Haven't seen - you're pulling our legs with this one, aren't you?

Nope! It's a pretty slick feature.
[tt]
Camera:

Select a range that you want displayed on another sheet or at some other range on the sheet.

left-Click the Camera Icon (a + appears)

Holding the left button, navigate to the sheet/cell you want to place the result and drag it onto the sheet -- it will expand/contract to the proper size.

Every change that happens in the source range, appears in the Camera PICTURE.
[/tt]
Good to use in a compound report where the columns in two tables to not align.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kjv1611]

Re: Camera
Very interesting indeed. I've done the same thing through other means in the past, but this seems MUCH quicker and easier.