5 nightmares for Excel and how to fix it

Trying to edit multiple files at the same time will become a 'Find worksheet' game for you. You probably know the feature you need is in one of the ribbon above but exactly which ribbon it is? A large workbook file can slow down the program a lot, making users spend a lot of time waiting when a certain task is done.

Let's face this fact: If you perform a significant amount of important work in Excel, chances are we will encounter successive nightmares. And if you find that using Excel makes it harder to check financial numbers than using paper prints, you may not have exploited and used this utility properly.

In this article we will give you some solutions to fix 5 crashes in Excel 2007 and 2010 . We will show you how to handle multiple workbooks effectively, speed up heavy files, track changes from multiple users, find essential features in countless ribbon and enter data in a way. easily.

1. Open multiple Workbooks effectively:

In Excel users will encounter two problems when working on three or more spreadsheets : encountering very small windows that do not provide a framework large enough or difficult to switch between these windows.

5 nightmares for Excel and how to fix it Picture 1

A series of windows for users to simultaneously view multiple workbook files

When you start Excel, the program will open a window on your Windows desktop. When a user opens or creates a workbook, this will open a window inside the Excel window. You can maximize the size of the internal windows so that they fill the entire Excel window, or can be restored to view all at once.

Unless the worksheets are too small, you should keep the internal windows maximized (default setting) so that they fill the entire Excel window. You can then switch between worksheets by pressing Ctrl-Tab or in the other direction Ctrl-Shift-Tab .

This method works well if you only open two files - but when adding more files, you need to cycle through them, if you choose to go in the wrong direction, it will take you more time to return. Another problem with this technique is that it does not allow you to simultaneously test two workbooks.

5 nightmares for Excel and how to fix it Picture 2

Minimize the spreadsheet window by clicking the second Restore icon.

Click the Restore button of the workbook, the button you can find under the Restore button of Excel in the upper right corner. You can resize and rearrange the windows to make it more suitable. It is also possible to minimize these windows when you do not want to use them anymore.

If you use two screens, you can click the Restore button of Excel so that the application will not be maximized, then drag the corner of the Excel window to fill both screens. Now you will have more space to arrange the windows.

The last option - best in our opinion - is to download and install one of ExtendOffice's Office Tab products. The Office add-ins allow each file to be opened in a separate tab at the top of the application window.

5 nightmares for Excel and how to fix it Picture 3

ExtendOffice 's Office Tab allows you to easily switch between open files

2. Heavy files:

A lot of cases a certain Excel workbook file is so heavy that we can't stand it. Here are three common causes and what you can do to fix them.

Too many calculations:

In complex workbooks, some changes can affect hundreds of related calculations and Excel will take a lot of time to handle this task.

The solution here is to turn off automatic calculations via the Options dialog. Click the File tab, then select Options in the left pane. In Excel 2007, click the Office button and then click the Excel Options button at the bottom of the drop-down menu.

When in the Options dialog box, select Formulas in the left pane. For the Workbook Calculations section, select Manual .

Just remember that after selecting this option, the numbers calculated from the formula will not give the correct result until you save the file or press F9 .

Too many formats:

A simple spreadsheet will not look good, but it should only use a few formatting options to beautify it.

5 nightmares for Excel and how to fix it Picture 4

If a spreadsheet is too slow, you can try deleting the formatting

To find out if the format slows down the file, copy it and open the copy in Excel. Select the entire worksheet by pressing Ctrl-A . If the workbook has multiple worksheets, press the Shift key while clicking the last tab at the bottom of the window to select all worksheets. Then, in the Editing section of the Home ribbon, click the drop-down arrow with the delete icon (which looks like a eraser) and select Clear Formats . Save file.

If this new file is significantly faster than the old file, removing the format is a must-do for the original file.

File is too large for what is needed:

Obviously a large file always shows a flicker. However, we can minimize some large files so they are lighter, work faster.

To find out if your file is having this problem, press Ctrl + End , which will take you to the last cell in the worksheet. Here you can see that it is below and to the right of the other cells, but Excel only stores them until you put something in it.

If the cell you just searched far below the last line or away from the right of the last column also contains content, your file contains unnecessary overhead.

The solution to this situation is to copy the actual used cells (with content) into a new worksheet in the same workbook, then delete the old worksheet.

3. Lost features in Ribbon components:

The ribbon interface that appears in Office 2007, is expected to make Excel and other applications easier to use. However, sometimes users are very time consuming to find out exactly which ribbon is needed for them.

5 nightmares for Excel and how to fix it Picture 5

Install Search Commands , you will never have to click through the ribbon to search for the desired feature

Search Commands, a free add-on of Microsoft Office Labs, will solve you this problem. When installing the add-on, it will present the ribbon where you can search for commands.

4. Follow changes from multiple users:

If you and a colleague work on the same workbook, you will have problems. If three or four people do the same file over the network, you will experience a major disaster.

Fortunately, everything you need to follow the workflow is right in the Review ribbon. Here you will see three essential tools on this ribbon that can help you.

See changes:

You will definitely want to see who changed what and when the change was made. Click on Track Changes menu and select Highlight Changes . Then fill in the options. You can control how long the changes will be saved, who changes need to be checked and decide whether they are listed or not. You can also accept or reject changes.

5 nightmares for Excel and how to fix it Picture 6

Your spreadsheet protection before allowing others to use it

Protect all or part of the file:

Options Protect Sheet and Protect Workbook allow you to control who can change what. Click either of these buttons, and you will be able to password-protect various aspects of the file.

Share the workbook:

The Share Workbook option allows multiple users to change a spreadsheet at the same time. Don't assume that this option will make everything worse because when a conflict occurs at the time you save the file, a dialog box will appear providing two options and allowing you to choose one of two options. choose that.

5. Easy data entry:

Spreadsheets are not database programs, but everyone uses them like that, enter columns and data rows into the table. Why not? Obviously, setting up a simple table in Excel is much easier than creating a real database in certain programs such as Access.

This is why not: The data entry process is much more difficult when done in Excel. In a real database, users will have input forms, which allows us to more easily perform when manipulating spreadsheet layout tables.

5 nightmares for Excel and how to fix it Picture 7

The final step: Add the Form icon to the Quick Access Toolbar .

So how to get such input form in Excel. This is very simple, you just need to click the down arrow at the far right of the Quick Access Toolbar , then select More Commands . In the drop down menu Choose commands from , select Commands Not in the Ribbon . Find and select the Form then click Add .

Now you can select any cell in the table, then click the Form icon. A real input form will appear.

5 nightmares for Excel and how to fix it Picture 8

Input form simplifies data entry

Good luck!