Excel Read Only Recommended Update The Spreadsheets

It is always a good idea to leave at least three blank rows above your table ( at least three, preferably more).

But many users are equally aware that dealing with workbooks can cause a huge number of snags.

The hacks in this chapter will help you avoid some of these snags while taking advantage of some of the more effective but often overlooked ways in which you can control your workbooks.

Before we leap into the hacks, though, its worth taking a quick look at some basics that will make it much easier to create effective hacks.

Excel is a very powerful spreadsheet application, and you can do incredible things with it.

Excel Read Only Recommended Update The Spreadsheets

Unfortunately, many people design their Excel spreadsheets with little foresight, making it difficult for them to reuse or update the spreadsheets theyve so carefully built.

In this section, we provide several tips you can follow to ensure that youre creating spreadsheets that are as efficient as possible.

The 8020 Rule Perhaps the most important rule to follow when designing a spreadsheet is to take a long-term view and never assume you will not need to add more data or formulas to your spreadsheet because chances are good that you will.

With that in mind, you should spend about 80 percent of your time planning your spreadsheet and about 20 percent implementing it.

Remember that spread-sheets are about making it easy for users to get correct information, not just about presenting information that looks good only once.

Structural Tips Without a doubt, the number one mistake most Excel users make when creating their spreadsheets is that they do not set up and lay out the data in the manner in which Excel and its features expect.

Here are, in no particular order, some of the most common mistakes users make when setting up a spreadsheet: Unnecessarily spreading data over many different workbooks Unnecessarily spreading data over numerous worksheets Unnecessarily spreading data over different tables Having blank columns and rows in tables of data Leaving blank cells for repeated data The first three items on the preceding list add up to one thing: you should always try to keep related data in one continuous table.

Time and time again we see spreadsheets that do not follow this simple rule and thus are limited in their ability to take full advantage of some of Excels most powerful features, including PivotTables, subtotals, and worksheet formulas.

In such scenarios, you can use these features to their full potential only when youve laid out your data in a very basic table.

It is no coincidence that Excel spreadsheets can comprise 1,048,576 rows (65,536 pre-2007) but only 16,384 columns (256 pre-2007).

If you find you are repeating the same data over and over for two or more rows in one of these columns, resist the temptation to use blank cells to indicate repetition.

Excel has a rich set of lookup and reference formulas, some of which require that your data be sorted in a logical order.

Sorting also will speed the calculation process of many functions significantly.

Formatting Tips Moving beyond structure, formatting also can cause problems.

Although a spreadsheet should be easy to read and follow, this should rarely be at the expense of efficiency.

Far too many people spend tremendous amounts of time formatting their spread-sheets.

Although they dont necessarily realize it, this time frequently comes at the expense of efficiency.

Often the overuse of formatting adds size to your workbook, and although your workbook might look like a work of art to you, it might look terrible to someone else.