Free Microsoft Excel 2013 Quick Reference

Icon sets conditional formatting Results

Alright, I wanted to add some bells and wistles to this Soccer Division Standings Spreadsheet we have been working on. What I had in mind was ranking the Goalkeepers in their division using the icon sets provided in Conditional Formatting.

I have a column with the number of shutouts each keeper has in their division. In the colum next to it I would like to use the traffic light icon set to show a green dot for the keeper with the highest number of goals, a yellow dot for the second highest number of goals and a red dot for the third highest number of goals in their division Here is the catch, if there are more than one keper with the same number of goals in any of these three three ranks, then each of these keepers would gets the same colored dot.

Something like this:

KeeperRankSHUTOUTSTrafiic LightsNate19GREENJoe26YELLOWAndrew42Nick35REDAlex42Sean26 YELLOW

I do not want to sort the shutout data since it is already sorted by team standings and I had hoped to keep the sheets format standard, but we could add another column if need be.

Thanks for any help on this, I have tried many combinations of usng the RANK, MAX, LARGE functions for the formula to implement in the icon set with not luck.

I am sure this must be a very common and easy setup but I have not found the answer yet, but I know you guys should eat this one up.

Thanks,
Joe

I subscribe to a newsletter where someone asked about having a graphic appear near a cell, based on the cell's answer. For example, say, if the cell had a nested-if formula and said, "Pear," a picture of a pear would appear; if an apple, that picture would appear, etc. I tried it, and it works. Someone else had suggested some changes to it, but I'm just posting my response here.

In any case, this is what I came up with in response to it:

First, you'll need to have one cell that will use an If-statement to display the word related to your picture. In this particular case, we'll say that cell A1 of the sheet you want to display the picture in - say Sheet1 - will use your IF-formula to display one of these words: "Apple", "Pear", or "Coconut". For simplicity's sake, let's use this formula in cell A1 (referencing the values in cell A2):

=IF(A2=1,"Apple",IF(A2=2,"Pear",IF(A2=3,"Coconut","")))

So if cell A2 has a value of 1, then cell A1 will say "Apple" and so forth.

Next, select (or add) a blank sheet in your workbook, which we'll say is Sheet2.

With your blank sheet (Sheet2) selected, go to Tools -> Options -> select the View tab (at the top) -> from the options, de-select (uncheck) the box for Gridlines -> and then click on the "OK" button, so that none of your Gridlines on this blank sheet show.

Now, go to Tools -> Customize -> select the Commands tab (at the top) -> select "Tools" from the left-hand box, then scroll down about 2/3rds the way in the right-hand box until you see a "camera" icon. Left-click and hold down your mouse button on the camera icon; then, drag the icon to your tool-bar (where all your other menu choices are at the top of the Excel program). (NOTE: Before releasing the icon, it should appear as a plus-sign "+". You can put it either at the top on the right-hand of your menu choices, or in between other tool bars.)

Between cells A1 and C9 of your new sheet (Sheet2), put a picture of a pear. Put a different fruit - an apple - between cells A10 and C18. Finally, put your third picture of a fruit - a coconut or whatever - between cells A19 and C27.

On your new sheet (Sheet2), select cells A1 to C9 by left-clicking and holding in cell A1 and then dragging your mouse until it's over cell C9 and all the cells in between are highlighted. Now, click on your new camera icon. The area around the picture will show little lines around it. And your mouse-pointer should become a plus-sign.

Now, go to the sheet where you want the picture placed (Sheet1). The plus-sign will still show. Left-click and hold your mouse button down, moving your mouse diagonally so that the outline of the picture is sized and in the place where you want it. When you release the mouse button, the first picture will appear in that spot. (You can click on this picture again to re-size it; and it is a dynamic picture, in that if you put a different picture in the newer sheet, it will be reflected here.)

Now, go to your menu at the top and select Insert -> Name -> Define. In the top box, type in "FruitName" (without quotes). In the "Refers To" box below, type in a formula that references cell A1 on your current sheet and the three different areas of your sheet that holds your pictures. Here's an example, using what was previously given:

=IF(Sheet1!$A$1="Pear",Sheet2!$A$1:$C$9,IF(Sheet1!$A$1="Apple",Sheet2!$A$10:$C$18,IF(Sheet1!$A$1="Coconut",Sheet2!$A$19:$C$27,"")))

When you're done with this, click on the "OK" button. (Note: Be sure to use the absolute references here, designated by the dollar-sign, and then to go back and check it afterwards to make sure it didn't change, as it initially changed to an entire-sheet reference in my initial trial since I didn't use absolute references.)

Now, click once on the "graphic" image that you've previously placed on your sheet (in Sheet1). If you look in the formula bar, it will say something like this:

=Sheet2!$A$1:$C$9

Change that formula to this:

=FruitName

Now, if cell A1 (of Sheet1) says "Pear" (without quotes), a picture of a Pear will be wherever you placed the "graphic" in Sheet1; and it will change to the other graphics also dependent on the text. (Or if the formula is kept intact in cell A1, it will change based on the number 1, 2, or 3 in cell A2.)

You're just about done: just a couple more things.

Select the sheet where the three graphics are kept (Sheet2). From the menu at the top, select Format -> Sheet -> and then Hide. This will prevent anyone from messing up your graphics, with the originals needing to be retained in their set spots.

You can also select Protection on Sheet1, and it will have no effect on your graphic changing, based on the cell's value (though you may want to unprotect certain cells if need be).

p.s. I'd recommend that you change your bitmap-image to a JPEG (or GIF), also, as it will take considerably less file-size to include a JPEG.

Hi all,

Just wondering if someone can help me with the following (see attached spreadsheet).

I want to display in the column marked "chg" the difference between this value and the one in the column marked 'position'.

So, I would like to have a forumla in each cell for "chg" which reviews 'position' values and the shows the difference and I wanted to use the icon set (green, orange and red arrows) next to the number to further reference the change.

I have tried this with conditional formatting to no avail as I'm told I cannot reference all cells; therefore I would really apprecaite some assitiance with this as I'm lost on how to achieve this.

Many thanks

If I use the Fill Color icon on the toolbar and set a cell color to red, then the color index equals 3. However, if I use Conditional Formatting and set the color of a cell to red, then the color index equals 2. Why the difference?

Brian

When i do conditional formatting using a specific farmula and save my document, it gives me a warning and after I close the file and then reopen it. Conditional Shading is allover the column where i applied conditional formatting. I have to go and again apply the conditional formatting.

CONDITIONAL FORMATTING > MANAGE RULES > EDIT RULE > OK > APPLY .

Please see the attachment for the warning it gives me when i save the file. I am trying to save in default file format i.e. excel workbook.

Compatibility Report for FALL 08 DATA FINAL.xlsx
Run on 5/19/2009 17:33

The following features in this workbook are not supported by earlier versions of Excel. These features may be lost or degraded when you save this workbook in an earlier file format.

Significant loss of functionality # of occurrences

Some cells have overlapping conditional formatting ranges. Earlier versions of Excel will not evaluate all of the conditional formatting rules on the overlapping cells. The overlapping cells will show different conditional formatting. 142
'AUG_SEPT'!G3:J28
'SEPT_OCT'!F3:J39
'SEPT_OCT'!F1:J2
'OCT_NOV'!F3:J120
'OCT_NOV'!F1:J2
'NOV_DEC'!F3:J100
'NOV_DEC'!F1:J2

One or more cells in this workbook contain a conditional formatting type that is not supported in earlier versions of Excel, such as data bars, color scales, or icon sets. 3
'SEPT_OCT'!A2:J2
'OCT_NOV'!A2:J2
'NOV_DEC'!A2:J2

Minor loss of fidelity

Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available. 25

User Interface
New User Interface
The new results-oriented user interface makes it easy for you to work in Microsoft Office Excel. Commands and features that were often buried in complex menus and toolbars are now easier to find on task-oriented tabs that contain logical groups of commands and features. Many dialog boxes are replaced with dropdown galleries that display the available options, and descriptive tooltips or sample previews are provided to help you choose the right option.
No matter what activity you are performing in the new user interface, whether its formatting or analyzing data, Excel presents the tools that are most useful to successfully complete that task.
Introducing the new interface
There is a new look for Office Excel 2007, a new user interface (UI) that replaces menus, toolbars, and most of the task panes from previous versions of Excel with a single mechanism that is simple and apparent. The new user interface is designed to help you be more productive in Excel, more easily find the right features for various tasks, discover new functionality, and be more efficient.
The New Interface commands hierarchy:
1. Menu Tabs.
2. Ribbons.
3. Groups within each tab break a task into subtasks.
4. Command buttons (icons) in each group carry out a command or display a menu of commands.
Ribbon user interface: The primary replacement for menus and toolbars in Office Excel 2007 is the Ribbon. Designed for easy browsing, the Ribbon consists of tabs that are organized around specific scenarios or objects. The controls on each tab are further organized into several groups. The Ribbon can host richer content than menus and toolbars can, including buttons, galleries, and dialog box content.
Tabs that appear only when you need them: In addition to the standard set of tabs that you see on the Ribbon whenever you start Office Excel 2007, there are two other kinds of tabs, which appear in the interface and are useful for the type of task you are currently performing.
Contextual tools: Contextual tools enable you to work with an object that you select on the page, such as a table, a picture, or a drawing. When you click the object, the pertinent set of contextual tabs appears in an accent color next to the standard tabs.
Program tabs: Program tabs replace the standard set of tabs when you switch to certain authoring modes or views, including Print Preview.
File Button : This button is located in the upper-left corner of the Excel window and opens the menu shown here:

Quick Access Toolbar: The Quick Access Toolbar is located by default at the top of the Excel window and provides quick access to tools that you use frequently. You can customize the Quick Access Toolbar by adding commands to it.
Adding Commands to Quick Access Toolbar: In the Customize Quick Access Toolbar box, select either For all documents (as a default) or a specific document.
Click the command that you want to add, and then click Add.

Dialog Box Launchers: Dialog Box Launchers are small icons that appear in some groups. Clicking a Dialog Box Launcher opens a related dialog box or a task pane, providing more options related to that group.

Use the Keyboard to Access any Commands in the Ribbon
To use keyboard shortcut: To open a menu tab, press the Alt tab, now press a letter(s) or a number or a combination of a letter & a number , see below:
Step 1: press the Alt key or F10.
Step 2:
Press H, and then a letter(s) or a number or a combination of both (a letter & a number).
Or
Use the Tab key to move between command buttons in the Ribbon.
Memory management, Workbook, Worksheet & Cells
Memory Management
Memory management has been increased from 1 GB of memory in Microsoft Office Excel 2003 to 2 GB in Office Excel 2007.
You will also experience faster calculations in large, formula-intensive worksheets because Office Excel 2007 supports dual-processors and multithreaded chipsets.
Numbers of Rows, Columns & Cells in a Worksheet
Excel 2007 sheet contains 1,048,576 rows by 16,384 columns, total of 17,180,033,024 cells compare to previous Excel versions which hold 65,536 rows by 256 columns, total of 16,777,216 cells.
New file formats
XML-based file format: In 2007 Microsoft Office system, Microsoft is introducing new files formats for Word, Excel, and PowerPoint, known as the Microsoft Office Open XML formats. These new file formats facilitate integration with external data sources, and also offer reduced file sizes and improved data recovery. In Excel 2007, the default format for an Excel workbook is the Office Excel 2007 XML-based file format (.xlsx). Other available XML-based formats are the Excel 2007 XML-based and macro-enabled file format (.xlsm), the Excel 2007 file format for an Excel template (.xltx), and the Excel 2007 macro-enabled file format for an Excel template (.xltm).
Themes, Colors & Formatting
Office themes
In Office Excel 2007, you can quickly format the data in your worksheet by applying a theme and by using a specific style. Themes can be shared across other 2007 Office release applications, such as Microsoft Office Word and Microsoft Office PowerPoint, while styles are designed to change the format of Excel-specific items, such as Excel tables, charts, PivotTables, shapes, or diagrams.
Number of Colors
Excel 2007 supports up to 16 million colors.
Rich conditional formatting
You can implement and manage multiple Conditional Formatting rules that apply rich visual formatting in the form of gradient colors, data bars, and icon sets to data that meets those rules. Conditional formats are also easy to apply in just a few clicks, you can see relationships in your data that you can use for your analysis purposes.
Formulas & Functions
Easy formula writing
Resizable formula bar: The formula bar automatically resizes to accommodate long, complex formulas, which prevents the formulas from covering other data in your worksheet. You can also write longer formulas with more levels of nesting than you could in earlier versions of Excel.
Function AutoComplete: With Function AutoComplete, you can quickly write the proper formula syntax. From easily detecting the functions that you want to use, to getting help completing the formula arguments, you will be able to get formulas right the first time and every time.
Easy access to Named ranges: By using Name manager, you can organize, update, and manage multiple Named ranges in a central location, which helps all users who need to work on your worksheet interpret its formulas and data.
New Functions
Very important and useful functions are added to Excel 2007. The functions are IFERROR, AVERAGEIF, AVERAGEIFS, SUMIFS and COUNTIFS. Read more and see example in Chapter 9, page 155.
New OLAP formulas and cube functions
When you work with multidimensional databases (such as SQL Server Analysis Services) in Excel 2007, you can use OLAP formulas to build complex, free form, OLAP data bound reports. New cube functions are used to extract OLAP data (sets and values) from Analysis Services and display it in a cell. OLAP formulas can be generated when you convert PivotTable formulas to cell formulas or when you use AutoComplete for cube function arguments when you type formulas.
Charts
A New look of charts
You can use new charting tools to easily create professional-looking charts that communicate information effectively. Based on the theme that is applied to your workbook, the new, up-to-date look for charts includes special effects, such as 3-D, transparency, and soft shadows.
The new user interface makes it easy to explore the available chart types so that you can create the right chart for your data. Numerous predefined chart styles and layouts are provided so that you can quickly apply a good-looking format and include the details that you want in your chart.
Visual chart element pickers: Beside the quick layouts and quick formats, you can now use the new user interface to quickly change any element of the chart to best present your data. In a few clicks, you can add or remove titles, legends, data labels, trendlines, and other chart elements.
A modern look with OfficeArt: Since charts in Excel 2007 are drawn with OfficeArt, almost everything you can do to an OfficeArt shape can also be done to a chart and its elements. For example, you can add a soft shadow or a bevel effect to make an element to stand out or use transparency to make elements visible that are partially hidden in a chart layout. You can also use realistic 3-D effects.
Clear lines and fonts: Lines in charts appear less jagged, and ClearType fonts are used for text to improve readability.
More colors than ever: You can easily choose from the predefined theme colors and vary their color intensity. For more control, you can also add your own colors by choosing from 16 million colors in the Colors dialog box.
Chart templates: Saving your favorite charts as a chart template is much easier in the new user interface.
Shared charting
Using Excel charts in other applications: In Excel 2007, charting is shared between Excel, Word, and PowerPoint. Rather than using the charting features that are provided by Microsoft Graph, Word and PowerPoint now incorporate the powerful charting features of Excel. Since an Excel worksheet is used as the chart data sheet for Word and PowerPoint charts, shared charting provides the rich functionality of Excel, including the use of formulas, filtering, sorting, and the ability to link a chart to external data sources, such as Microsoft SQL Server and Analysis Services (OLAP), for up-to-date information in your chart. The Excel worksheet that contains the data of your chart can be stored in your Word document or PowerPoint presentation, or in a separate file to reduce the size of your documents.
Copying charts to other applications: Charts can be easily copied and pasted between documents or from one application to another. When you copy a chart from Excel to Word or PowerPoint, it automatically changes to match the Word document or PowerPoint presentation, but you can also retain the Excel chart format. The Excel worksheet data can be embedded in the Word document or PowerPoint presentation, but you can also leave it in the Excel source file.
Animating charts in PowerPoint: In PowerPoint, you can more easily use animation to emphasize data in an Excel-based chart. You can animate the entire chart or the legend entry and axis labels. In a column chart, you can even animate individual columns to better illustrate a specific point. Animation features are easier to find and give you much more control. For example, you can make changes to individual animation steps, and use more animation effects.
Sorting, Filtering & Tables
Improved sorting and filtering
You can now sort data by color and by more than 3 (and up to 64) levels. You can also filter data by color or by dates, display more than 1000 items in the AutoFilter dropdown list, select multiple items to filter, and filter data in PivotTables.
Excel table enhancements
You can use the new user interface to quickly create, format, and expand an Excel table (known as an Excel list in Excel 2003) to organize the data on your worksheet so that its much easier to work with.
PivotTables
Easy-to-use PivotTables
By using the new PivotTable user interface, the information that you want to view about your data is just a few clicks away. You no longer have to drag data to drop zones that arent always an easy target. Instead, you can simply select the fields that you want to see in a new PivotTable field list.
After you create a PivotTable, you can take advantage of many other new or improved features to summarize, analyze, and format your PivotTable data.
Sharing & Connections
New ways to share your work
Using Excel Services to share your work: If you have access to Excel Services, you can use it to share your Office Excel 2007 worksheet data with other users, such as executives and other stakeholders in your organization. In Excel 2007, you can save a workbook to Excel Services and specify the worksheet data that you want other people to see. In a browser (browser: Software that interprets HTML files, formats them into Web pages, and displays them. A Web browser, such as Microsoft Internet Explorer, can follow hyperlinks, transfer files, and play sound or video files that are embedded in Web pages.), they can then use Microsoft Office Excel Web Access to view, analyze, print, and extract this worksheet data. They can also create a static snapshot of the data at regular intervals or on demand. Excel Web Access makes it easy to perform activities, such as scrolling, filtering, sorting, viewing charts, and using drill-down in PivotTables. You can also connect the Excel Web Access Web Part to other Web Parts to display data in alternative ways. And with the right permissions, Excel Web Access users can open a workbook in Excel 2007 so that they can use the full power of Excel to analyze and work with the data on their own computers if they have Excel installed.
Using this method to share your work ensures that other users have access to one version of the data in one location, which you can keep current with the latest details. If you need other users, such as team members, to supply you with comments and updated information, you may want to share a workbook the same way.
Quick connections to external data
You no longer need to know the server or database names of corporate data sources. Instead, you can use Quick Launch to select from a list of data sources that your administrator or workgroup expert has made available for you. A connection manager in Excel allows you to view all connections in a workbook and make it easier to reuse a connection or to substitute a connection with another user.
Printing
Better printing experience
Page Layout View: In addition to the Normal view and Page Break Preview view, Excel 2007 provides a Page Layout View. You can use this view to create a worksheet while keeping an eye on how it will look in printed format. In this view, you can work with page headers, footers, and margin settings right in the worksheet, and place objects, such as charts or shapes, exactly where you want them to be. You also have easy access to all page setup options on the Page Layout tab in the new user interface so that you can quickly specify options, such as page orientation. Its easy to see what will be printed on every page, which will help you avoid multiple printing attempts and truncated data in printouts.
Saving to PDF and XPS format: Like other 2007 Office release applications, Excel 2007 supports saving a workbook to a high-fidelity fixed file format, such as Portable Document Format (PDF) or XML Paper Specification (XPS) format, that encapsulates how it will look when it is printed. This allows you to share the content of your workbook in a format that is easy for other people to view online or print, without including the underlying formulas, external data queries, or comments.
Actually, this is what you did in earlier versions of Excel to collect the information you need before you save it to Excel Services.
Using Document Management Server: Excel Services can be integrated with Document Management Server to create a validation process around new Excel reports and workbook calculation workflow actions, such as a cell-based notification or a workflow process based on a complex Excel calculation. You can also use Document Management Server to schedule nightly recalculation of a complex workbook model.

Although I like a lot the way that Excel handles data analysis (and
especially the Pivot Tables) I think that there is still significant "room"
for improvement.

Here is what I suggest:

Regarding FILTERS we need the following improvements on functionality and
ergonomy:

1) to be able to formulate more than 2 conditions - probably 10 would be ok;
I know that this could be accomplished by using Advanced Filter but this is
very time-consuming
2) possibility to create conditions that relate current cell with the
coresponding cell from other column
3) to filter after more than one value and on multiple columns when we click
on "AutoFilter" icon - according to the cells we have selected while keeping
CTRL pressed
4) to obtain a "Show excluding" filter when we click on "AutoFilter" icon
while pressing Shift
5) a corresponding button to "Show All" which enables us to remove only the
filter of the current column (or selected columns) - a shortcut key like F6
would also be very useful - so if we have 5 active filters we could easily
cancel one and keep the other 4
6) to be able to see (and activate) each one of the last 10 different filter
conditions used when we click on the filter arrow attached to the header cell
of a filtered column - this could be shown either on the left or the right
side
7) to be able to save and load certain set of filters - this is very
important when you work with more than 3 custom filters and occasionally
press "Show All" or when you need to switch between different sets of filters
8) to view the current filter condition when we float the mouse pointer over
the column's header cell
9) enhanced visibility for the active filters - not only the small blue
arrow on the grey square

Regarding PIVOT TABLES:

1) to save the pivot table "settings" in order to be able to switch quickly
from one "look" to another - I often have to make at least 4 different copies
of the same pivot in order to quickly show the desired aspects of different
analysis (on the same data set) to my superiors
2) when we make a copy of one pivot table in the same workbook Excel should
base the new pivot, by default, on the "database" used by the original pivot;
3) when we double-click on one of the detail fields the new sheet that
appears should keep the same formatting as the "database" sheet; by
formatting I also refer to the settings like Group/Ungroup and Freeze Panes
4) it is extremely necessary to have a function that allows us to copy the
data from the TOTAL field in a pivot table to a special field in the pivot's
"database"; this would help us when we need to group the data we analyse in
different categories according to the sum in that speciffic field - one usage
is when we need to separate the products that were sold above a minimum
value (or quantity); at this time we can only run a very slow SUMIF on all
data or a VLOOKUP over the pivot
5) there are many situations when our pivot table is based on an enormous
database but we only need to study just a part of it at a time - it would
help us a lot to be able to automatically generate a pivot table with the
same design as the main pivot but based on our specific selection from it
6) each pivot table should be able to auto-freeze its header according to
the changes we brought to its page, column, row and data fields

Other issues:

1) the status bar should be able to display simultaneously at least 3
informations chosen by user: SUM, COUNT and NO DUPLICATES (something like
MODE function) would be a good default option
2) we need an enhanced VLOOKUP function that would be able to search in a
database by more that one single criteria (example: NAME + SURNAME) and to
return more than one field (example: AGE, COUNTRY, SALARY, JOB); I know that
multi-criterial search could be accomplished through DGET but this works only
for one record at a time
3) we need to have an option that would automatically re-order the columns
on a second table based on the columns in the master table. This is extremely
useful when you have to append periodically some data to an existing table or
to merge two tables in order to run an agregated pivot. Please note that this
can't be achieved through horizontal sort since the header of a table isn't
usually sorted alphabetically.

Above is what I consider that would help me, my colleagues and a lot of
power users that I know to use Excel more efficiently. Please let me know
what do you think about my suggestions.

Thank you very much for your attention.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Although I like a lot the way that Excel handles data analysis (and
especially the Pivot Tables) I think that there is still significant "room"
for improvement.

Here is what I suggest:

Regarding FILTERS we need the following improvements on functionality and
ergonomy:

1) to be able to formulate more than 2 conditions - probably 10 would be ok;
I know that this could be accomplished by using Advanced Filter but this is
very time-consuming
2) possibility to create conditions that relate current cell with the
coresponding cell from other column
3) to filter after more than one value and on multiple columns when we click
on "AutoFilter" icon - according to the cells we have selected while keeping
CTRL pressed
4) to obtain a "Show excluding" filter when we click on "AutoFilter" icon
while pressing Shift
5) a corresponding button to "Show All" which enables us to remove only the
filter of the current column (or selected columns) - a shortcut key like F6
would also be very useful - so if we have 5 active filters we could easily
cancel one and keep the other 4
6) to be able to see (and activate) each one of the last 10 different filter
conditions used when we click on the filter arrow attached to the header cell
of a filtered column - this could be shown either on the left or the right
side
7) to be able to save and load certain set of filters - this is very
important when you work with more than 3 custom filters and occasionally
press "Show All" or when you need to switch between different sets of filters
8) to view the current filter condition when we float the mouse pointer over
the column's header cell
9) enhanced visibility for the active filters - not only the small blue
arrow on the grey square

Regarding PIVOT TABLES:

1) to save the pivot table "settings" in order to be able to switch quickly
from one "look" to another - I often have to make at least 4 different copies
of the same pivot in order to quickly show the desired aspects of different
analysis (on the same data set) to my superiors
2) when we make a copy of one pivot table in the same workbook Excel should
base the new pivot, by default, on the "database" used by the original pivot;
3) when we double-click on one of the detail fields the new sheet that
appears should keep the same formatting as the "database" sheet; by
formatting I also refer to the settings like Group/Ungroup and Freeze Panes
4) it is extremely necessary to have a function that allows us to copy the
data from the TOTAL field in a pivot table to a special field in the pivot's
"database"; this would help us when we need to group the data we analyse in
different categories according to the sum in that speciffic field - one usage
is when we need to separate the products that were sold above a minimum
value (or quantity); at this time we can only run a very slow SUMIF on all
data or a VLOOKUP over the pivot
5) there are many situations when our pivot table is based on an enormous
database but we only need to study just a part of it at a time - it would
help us a lot to be able to automatically generate a pivot table with the
same design as the main pivot but based on our specific selection from it
6) each pivot table should be able to auto-freeze its header according to
the changes we brought to its page, column, row and data fields

Other issues:

1) the status bar should be able to display simultaneously at least 3
informations chosen by user: SUM, COUNT and NO DUPLICATES (something like
MODE function) would be a good default option
2) we need an enhanced VLOOKUP function that would be able to search in a
database by more that one single criteria (example: NAME + SURNAME) and to
return more than one field (example: AGE, COUNTRY, SALARY, JOB); I know that
multi-criterial search could be accomplished through DGET but this works only
for one record at a time
3) we need to have an option that would automatically re-order the columns
on a second table based on the columns in the master table. This is extremely
useful when you have to append periodically some data to an existing table or
to merge two tables in order to run an agregated pivot. Please note that this
can't be achieved through horizontal sort since the header of a table isn't
usually sorted alphabetically.

Above is what I consider that would help me, my colleagues and a lot of
power users that I know to use Excel more efficiently. Please let me know
what do you think about my suggestions.

Thank you very much for your attention.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc


No luck finding an answer? You could always try Google.