The Ultimate Guide to Excel Tips & Tricks

This article on Excel tips and tricks aims to offer you as much information as possible. These MS Excel tips and tricks may seldom appear anywhere else as Bright Hub has carefully compiled several articles that help you make the most of your favorite spreadsheet program.

MS Excel needs no introduction. Ever since the history of MS Excel, people use the spreadsheet to enter, manipulate and analyze data – as such or by way of charts. No matter whether you are a beginner, intermediate or an expert user, you will find something useful here. Did you know that MS Excel was first developed for the Mac Operating system? Strange, but true! It later became an indispensible part of the Microsoft desktop-based Office Automation Software, the MS Office Suite. Office 2010, the latest version as of now, also offers a cloud based version so that people can edit their documents from anywhere in the world.

Let us now focus on some Excel tips and tricks that might help you enhance your experience on the spreadsheet application.

Lists are related data arranged in one or more rows. You can create lists using the Create List command under Data tab. These lists can be arranged into drop down lists, which are especially helpful when you are using the Excel sheet for data entry. You can create custom drop down lists in Excel using the Data Validation tool.

You might also wish to create dependent lists. These lists are populated based on the selection in the first list. For example, the first list comprises of country names. Once the user selects the country name, the second list is populated with the names of states in that country. These lists are dependent on another list. Creating dependent lists is easy in MS Excel against creating lists using programming languages.

Sometimes, users of your Excel forms might enter a wrong answer. In such case, it helps if you can display a custom message that tells how to select or enter a value. Excel allows you to create custom input messages and even error alerts.

Excel provides just a small dialog box for formatting. However, you can do wonders when you know how to use it properly. Be it a normal report, a form, or charts, you have plenty of options to format the spreadsheet – data, charts, and other elements such as images, sound etc.

Charts are an important element when you are performing an analysis of data. You can create charts with one click in Excel. You can add a chart to the current worksheet or you can add it as a separate page in the workbook. It is easier to create chart sheets first and then change the chart sheet to embedded chart. This helps in better formatting of the spreadsheets.

For those who are new to Excel charts, we have an exclusive article. Please read “Tips and Tricks for Creating Charts and Graphs in Microsoft Excel” by Michelle McDonough to get started with charts in MS Excel.

Plenty of other articles exist on Bright Hub to help you with formatting. Here are some of them:

Customize Anything on a Chart with Right-click

Add New Data to a Chart

Display Profitability in a Profit Waterfall Chart

Copy Formatting to a New Range

Copy Cell Formatting, Including Column Widths

Hide and Unhide Data

Use White Color for Fonts to Hide Data

Add a Printable Background to Your Spreadsheet

Change the Background of the Worksheet

Change Width of All Columns in One Command

Use the Border Tab in the Format Cells Dialog

Add a Page Break at Each Change in Customer

Draw an Arrow to Visually Illustrate That Two Cells Are Connected

Add an AutoShape to Your Worksheet

Draw More Than the Four Basic Shapes

Add Text to an AutoShape

Add a Shadow to an AutoShape

Add a 3-D Effect to an AutoShape

These are just some of the articles related to formatting in Excel. You can search for more Excel tips and tricks on formatting using the search feature on top of each page at Bright Hub.

As stated on the previous page, MS Excel offers more potential than you can imagine. Bright Hub contains more than 277 Excel tips and tricks written by Bill Jelen. These articles form part of his book “Learn Excel with Mr. Excel.” We have laid out these articles in four parts for easier understanding: Excel Basics; Dealing with Data; Dealing with Formatting; and Wrangling.

If you are new to MS Excel, you might want to look at the Basics of Excel. The article compilation also aims to empower intermediate and expert users with tips and tricks to understand the MS Excel environment so that they can save time. It includes articles on shortcuts to Excel commands; how to open a particular workbook each time you start MS Excel; using macros to automate your tasks and many more. The Excel Environment not only helps you explore Excel completely, it also helps you save time by working faster. Please note that some of these articles refer to menus of Excel 2003 or previous versions. However, except for the articles dealing with Excel menus (such as opening full menus each time); others are valid for Excel 2007 and Excel 2010 as well.

The section, “Calculating with Excel,” contains approximately 91 Excel tips and tricks that help with calculations. It also covers topics such as Playing Dice Games with Excel; Play Craps with Excel; Create Random Numbers; Calculate Loan Payments; How to Figure out Lottery Probability and more. Check out the complete list and access the articles at Calculating with Excel.

The third section offers tips and tricks for wrangling data in Excel. This section contains articles covering topics as simple as How to Sort your Data to Understanding Pivot Tables. It also includes articles on topics such as My Manager wants Subtotals above Data; General Protection Faults; Adding Blank Line after Every Subtotal; and more. To access the table of contents and the articles, please read Wrangling Data.

The final section of the collection on Excel tips and tricks covers formatting. Some of the topics under this section were mentioned in the previous page. Check out the entire list of topics and links to articles at Formatting Tips from Mr. Excel.

The four sections contain 277 Excel tips and tricks for you to master MS Excel and make the most out of it. Please let us know if you benefited with this tutorial using our comments section.