Advanced topics, such as working with scenarios, macros, lookup functions, pivot tables, and auditing formulas can be found in this category. These topics are explained and covered in a variety of ways including FAQs, tips, step by step tutorials, and general information articles on the topics.
Apply data validation to cells
Data validation is an Excel feature that you can use to define restrictions on what data can or should be entered in a cell. You can configure data validation to prevent users from entering data that is not valid. If you prefer, you can allow users to enter invalid data but warn them when they try to type it in the cell. You can also provide messages to define what input you expect for the cell, and instructions to help users correct any errors.
Excel Conditional Formatting
Excel's conditional formatting features let you control how a cell is formatted - such as changing colors - depending on the data the cell contains. If the cell data meets the conditions you set, then the formatting is applied to the cell. The tutorials in this list cover a number of options for using conditional formatting in Excel and include.
PivotTable reports
an interactive table that automatically extracts, organizes, and summarizes your data. You can use this report to analyze the data, make comparisons, detect patterns and relationships, and discover trends..
Insert or delete a drop-down list
To make data entry easier in Excel, or to limit entries to certain items that you define, you can create a drop-down list of valid entries that is compiled from cells elsewhere in the workbook. When you create a drop-down list for a cell, it displays an arrow in that cell. To enter information in that cell, click the arrow, and then click the entry that you want.
Styles in Excel
Microsoft has made it very easy to dress up your worksheets with all sorts of fill patterns, borders and other frills. Because formatting of cells is often done in an ad-hoc manner, many spreadsheet pages look messy. By consistently using cell styles (instead of changing parts of the cell's formatting) you will be forced to think about the structure of your work. Religiously using styles may even force you to reconsider the overall structure of the entire spreadsheet model: The quality of the computational model itself may be positively affected.
I therefore consider Styles as being underused, underestimated and under exposed.
Importing text files in an Excel sheet
f you've ever tried to open a text file, you have probably used the Text import wizard.
If you have ever opened a CSV file, you have probably noted you can't set up how it gets imported. The text import wizard does not start for these file types.
What if you have the situation where you get files with similar structure, which need the same text import settings over and over?
How do you import the data from those files into the same worksheet each time, without having to re-define the import settings over and over again?
I'll tell you.
COUNT with Multiple Conditions
There are many times that it become necessary to COUNT cells based on multiple criteria. The examples in the articles will show you 3 ways that this can be done. However, often the most efficient method is to use a Pivot Table If you are not familiar with Pivot Tables, I cannot stress enough how much easier spreadsheet life becomes once you are!
MORE
SUM with Multiple Conditions
Using Array Formulas
Lookup Function - Part I
Lookup Function - Part II
Introduction to Macro Record in Excel 2007
and more
Everything and always updated at http://advanced-excel.blogpsot.com
