Excel Tutorials and Tips, Part 1

Here is my first post of the Excel Tutorials and Tips at excel.tips.net that cover many topics that will help make you a professional at Excel. Excel is a spreadsheet application from Microsoft’s Office suite that creates and manages business transactions that deal with accounting. The below Excel tutorials and tips are a good place to explore  and learn the basics and also some advanced features of Microsoft Excel. These are the best ones in each topic from our review of this site. (Here is my part 2 post of the Excel learning material from this site.)


Conditional Formatting in Excel:
Conditional formatting allows you to set rules for cell formatting. If the rules are met, then the formatting is applied. The tutorials below explain how Conditional formatting work.
 - Conditional Formatting
 - Adding Ordinal Notation to Dates
 - Applying Conditional Formatting to Multiple Worksheets
 - Conditional Formats that Distinguish Blanks and Zeroes
 - Conditionally Formatting for Multiple Date Comparisons
 - Shading a Cell Until Something is Entered
 - Highlighting Cells Containing Specific Text
 - Counting Colors from Conditional Formats
 - Sorting or Filtering by Conditional Format Results
 - Conditional Formatting with Data Imported from Access

Customizing Excel:
Excel makes it easy for you to personalize and customize the menus and toolbars to suit each individuals way of working. The tutorials below explain the level of customization a user can achieve.
 - Adding a Little Animation to Your Life
 - Colors and Fonts for Worksheet Tabs
 - Creating Superscript and Subscript Buttons
 - Specifying the Behavior of the Enter Key
 - Adding Your Own Menu Items
 - Removing Items from a Context Menu
 - Buttons for Workbooks
 - Customizing a Toolbar
 - Renaming a Toolbar
 - Saving Valuable Toolbar and Screen Space

Excel’s Custom Views:
Many of us would also like to extend the level of customization to having pur own Views and these tutorials below explain how to do just that.
 - Arranging Workbook Windows
 - Additional Ways of Displaying a Hidden First Column
 - Arranging Workbook Windows
 - Changing the Size of Spreadsheet Panes
 - Displaying a Hidden First Column
 - Getting Rid of Spreadsheet Panes
 - Splitting Your Spreadsheet Window Into Panes
 - Working With Multiple Workbooks

Editing in Excel:
While editing a sentence may seem easy, editing in Excel takes on a whole new approach. The links below explain editing in Excel.
 - Automatically Breaking Text
 - Checking for a Value in a Cell
 - Combining Multiple Rows in a Column
 - Counting Words
 - Easily Entering Dispersed Data
 - Easily Entering Dispersed Data
 - Transposing Your Data
 - Making AutoComplete Work for an Entire Column
 - Using an Input Mask
 - Ranges on Multiple Worksheets

Excel Filtering:
Excel provides filters to quickly find data in an Excel sheet. Filtering does not change data in an Excel sheet, and all data appears the same as before as soon as the filter is removed. The tutorials below provide an in-depth coverage of filters.
 - Counting Filtered Rows
 - Removing Duplicates Based on a Partial Match
 - Extracting Targeted Records from a List
 - Performing Calculations While Filtering
 - More Than Two Conditions for Custom AutoFiltering
 - Suppressing Zero Values in PivotTables
 - Sorting or Filtering by Conditional Format Results
 - Advanced Filtering
 - Limits to Filtering
 - Using AutoFiltering

Find and Replace in Excel:
Find and Replace is a common tool used in most applications, and Excel is no exception. The tutorials given below explain how to use the Find and Replace tool effectively in Excel.
 - Checking for Messages in Cells
 - Finding and Deleting Links
 - Limiting Searching to a Column
 - Searching for Leading Apostrophes
 - Searching for Line Breaks
 - Searching for Wildcards
 - Using Find and Replace to Pre-Pend Characters
 - Wildcards in ‘Replace With’ Text

Formatting in Excel:
To better understand raw data, it must be formatted to some specific criteria. The tutorials below explain how formatting can be applied to cells in an Excel sheet.
 - Formatting Raw Data
 - Partially Blocking Social Security Numbers
 - Removing All Formatting
 - Understanding Date and Time Formatting Codes
 - Making All Occurrences Bold
 - Drawing Borders
 - Automatically Breaking Text
 - Displaying Negative Percentages in Red
 - Matching Formatting when Concatenating
 - Understanding Cell Indenting

Excel Formulas:
Data available in Excel sheets can be manipulated using Formula. Different cells can be grouped together to have their values computed in any fashion. The links below explain how to work with Formula.
 - Activating the Formula Bar with the Keyboard
 - Entering Formulas in Excel
 - Automatically Numbering Rows
 - Calculating Statistical Values on Different-Sized Subsets of Data
 - Calculating the Median Age of a Group of People
 - Complex Lookup Formulas
 - Counting Consecutive Negative Numbers
 - Dealing with Circular References
 - Determining “Highest Since” or “Lowest Since”
 - Saving Common Formulas

Excel Macros:
A macro is usually a set of instructions that are grouped together to carry out small, repetitve tasks. Macros can also be created and used extensively in Excel and these tutorials explain how they work.
 - Adding a Macro to a Toolbar
 - Understanding Functions in Macros
 - Editing Macros
 - Comparing Strings
 -  Creating and Naming a Worksheet Using a Macro
 -  Developing Reciprocal Conversion Formulas
 -  Displaying Messages When Automatic Data Changes
 -  Getting Rid of the “Enable Macros” Notice
 - Macros in Template Files
 - Putting an X in a Clicked Cell

Page Setup:
Put simply, a page in a spreadsheet application can be setup to print values in the landscape or the portrait mode. The page setup also includes page breaks, headers and footers and inserting page numbers. These tutorials explain all these concepts detail.
 -  Changing Paper Size for a Complete Workbook
 - Multiple Line Headers and Footers
 - Forcing a Page Break
 - Turning Headers On and Off
 - Dynamic Headers and Footers
 - Putting a Graphic in a Header or Footer
 - Inserting the Saved Date In a Header or Footer
 - Recognizing a Header Row when Sorting
 - Adding Page Numbers
 - Changing Page Orientation

Printing:
An Excel sheet can be setup to be printed in ways that suit a user. An Excel sheet can even be printed without opening it. These and more printing related tutorials can be found below.
 - Automatically Printing a Range
 - Conditional Printing
 - Hiding Errors on Printouts
 - Printing a Portion of a Worksheet
 - Printing Based on Cell Contents
 - Printing Comments
 - Printing without Opening
 - Protecting Print Settings
 - Repeating Rows at the Bottom of a Page
 - Setting Print Titles

Excel Shortcut Keys:
As with an application, Excel too has a lot of shortcut keys that permit quick and easy navigationn on a worksheet. You can find all Excel shortcuts in these tutorials.
 - A Shortcut for Switching Focus
 - Canceling a Command
 - Changing Font Size Using a Shortcut Key
 - Closing All Open Workbooks
 - Defining Shortcut Keys for Symbols
 - One Shortcut for Two Macros.
 - Shortcut to Merge Cells

Subtotals in Excel:
Subtotals of a column can be calculated and stored for quick reference. The tutorials below help understand Subtotals better.
 - Copying Subtotals
 - Counting with Subtotals
 - Formatting Subtotal Rows
 - Moving Subtotals
 - Problems with Nested Subtotals
 - Subtotals Option Grayed Out.
 - Using Subtotals and Totals

Worksheet Functions:
In Excel worksheets, functions can be implemeted on the data. These functions include database functions, date and time functions and text functions to name a few. The tutorials help explain the concept of worksheet functions.
 - Calculating Fractions of Years
 - Counting Unique Values with Functions
 - Functions Within Functions.
 - Looking Up Names when Key Values are Identical
 - Determining Columns in a Range
 - Counting with Two Criteria
 - Exiting a For … Next Loop Early
 - Understanding the VLOOKUP Function
 - Using the IF Worksheet Function
 - Calculating Business Days

Excel Worksheets:
A single Excel file can hold multiple worksheets. The tutorials below explain the manipulations that can be carried out on Excel worksheets.
 - Condensing Multiple Worksheets Into One
 - Ordering Worksheets Based on a Cell Value
 - Saving Versions
 - Protecting Worksheets.
 - Sheets for Days
 - Functioning Check Boxes in a Protected Worksheet
 - Changing Excel’s Background Color
 - Synchronized Workbook and Worksheet Names
 - Hiding and Unhiding Worksheets
 - Determining a Worksheet’s Number

Excel’s Environment and Interface:
The folowing tutorials help you to get accustomed to Excel’s Environment and Interface.
 - Getting Rid of the Startup Spreadsheet
 - I Hate the Excel 2007 User Interface
 - Precision and Number Display
 - Precision in Excel
 - Saving Non-Existent Changes
 - Where is Toolbar Customization Information Stored?
 - Changing the Size of Spreadsheet Panes
 - Getting Rid of Spreadsheet Panes
 - Keep Your Headings in View
 - Splitting Your Spreadsheet Window Into Panes

Popularity: 27% [?]