Common Microsoft Excel Problems – Solved!

Here is the list of Common Microsoft Excel Problems – with solutions.

Train Excel not to jump to wrong conclusions. Sometimes Excel decides to format data in ways you don’t want. For example, if you enter a product code like this—10-11-12—Excel will display it as a date. And if you need to enter 012345, Excel drops the leading zero. The solution is to type an apostrophe before the number. This won’t prevent Excel from misinterpreting many fractions (like 3/16) as dates, though. If you enter this into a cell with the leading apostrophe, it will display almost correctly (toward the left of the cell, not the right), but you’ll get nonsense results if you use the cell in a formula.

The solution here is to add a leading zero and space, like this: 0 3/16. To recover your original entry, you can try pressing Ctrl-Shift-~ (tilde) to apply general formatting, but you’ll probably have to retype the whole cell.

Apply formatting to part of a cell. Press F2 to edit the content of a cell, then select the part of the cell that you want to format and either press a formatting shortcut key, such as Ctrl-B or Ctrl-I, or use Format | Cells to apply formatting to the selected part of the cell.

Enter credit card numbers in Excel. Because of the way it stores numbers, Excel doesn’t allow more than 15 significant digits in cells, and when you enter longer numbers, it usually reformats them or appends a zero. If you need to display a long string of numerals, like a credit card number, precede it with an apostrophe.

Use the Ctrl key to work with nonsequential cells. You can paste or type the same data into multiple, nonsequential cells by holding down the Ctrl key while selecting cells with the mouse; press Ctrl-V to paste the contents of the Clipboard into all the cells, or press Ctrl-Enter after typing to enter typed text into all cells. Use the same technique to apply the same formatting to nonsequential cells.

Use the keyboard to select non-sequential cells. Don’t like the mouse? Select nonsequential cells by moving to the first cell you want to select; press F8 to turn on Selection mode (EXT appears in the status bar); if you want to start by selecting only one cell, press Shift-F8 to switch into Add mode (ADD appears in the status bar); if you want to start by selecting two or more adjacent cells, move the cursor to the last of the adjacent cells, then press Shift-F8. Now move to the nonsequential cells you want to add to the selection; press F8 to turn on Selection mode, and follow the same steps as before to add cells to the selection. Finish by pasting the same data into all cells, or typing data and pressing Ctrl-Enter, or applying formatting.

Also read 10 Common Spreadsheet Mistakes You’re Probably Making

Change the effect of the Enter key. When you press the Enter key, Excel moves to the next cell down. But what if you want to stay in the same cell? You can make the Enter key take you in any other direction, or leave you in the same cell, by using Tools | Options | Editand changing the Move selection after Enter setting.

Add line breaks to separate text in one cell. If you’re used to the Shift-Enter keystroke that enters a line break in Word, this one may confuse you. To add a line break in an Excel cell, press Alt-Enter. (In Excel, Shift-Enter moves the selection in the reverse direction defined for the Enter key; see “Change the effect of the Enter key.”)

Define your own constants. Use the Insert | Name | Define… command to replace cryptic cell references with easy-to-remember names for your constants. For instance, the annuity calculation =Payment*((1+Rate)^Period-1)/Rate is a lot easier to interpret than =B3*((1+B1)^B2-1)/B1.

In the command, the Refers to… field defaults to using the name to refer to the current cell, but you can replace the cell address with any range, text, number, or formula. This name can now be used in any other formula in the worksheet.

One application for this technique is to define a range that is relative to whatever the current cell might be, like this: Create a range named AllAbove and enter this in the Refers to… box:=OFFSET(INDIRECT(“R1C”,),,,ROW()-1). Now define a range named S as =SUM(AllAbove). Then simply enter =S in any cell for a sum of all cells above it.

Start with one worksheet, not three. By default, Excel creates new spreadsheets with three sheets. Change this number to one or anything else using Tools | Options | General, and set the number for Sheets in New Workbook.

Add text to a number in a cell. You can add explanatory text to a numerical value in a cell without affecting the way the number is used in formulas, sums, and other features.

Select the cells to which you want to add specific text; choose Format | Cells | Number; in the Category list choose Custom; in the Type list choose General, and in the field at the top, leave the word General untouched and enter (in quotation marks) any text that you want to appear before or after the number. For example: “all” General “widgets” will appear in your spreadsheet with the number 42 (or whatever number you enter or is supplied by a formula) as all 42 widgets.

Copy scalable, high-resolution images of worksheets and charts. If you want to reproduce part of a spreadsheet in a presentation or document, don’t simply copy and paste the cells, because the target application (PowerPoint, Word, or anything else) may receive an unwieldy Excel “object,” and you probably won’t be able to resize it to fit the page.

Instead, select what you want to copy as a picture (you can even use more data than you can see on screen). Hold down Shift as you click Edit; the Copy command will become the Copy Picture… command; choose it, and select the option to copy As shown on screen andPicture Format.

When you paste the picture into a document, it will be copied as a scalable Windows Metafile. If you forget to use Copy Picture… and copy cells directly into Word or PowerPoint, a SmartTag icon appears at the lower right with options that let you decide how to display the cells.

Also read 10 advanced formatting tricks for Excel users

Avoid broken lines in line charts. Excel creates broken or misleading line charts if data is missing from one or more cells. If a cell is blank, the line has a gap; if the cell has non-numeric data, Excel treats it as zero. Solve the problem by inserting the formula =NA(), meaning Not Available. Excel will create a continuous line between the two cells on either side of the not-available data.

Force blank cells to display zeroes. Excel doesn’t always display zeroes in blank cells. To make blank cells display as zeroes, first type 1 in an unused cell and copy the cell to the Clipboard. Highlight the region that includes blanks that you want converted; select Edit | Paste Special…, select Multiply, and click OK. Excel multiplies all the entries in the region by 1, so existing cells are unchanged and blank ones display 0.

Avoid false positives in search strings. When you try to search for the string 123*789, Excel insists on showing you a cell that contains 123456789. That’s because Excel interprets the asterisk as a wildcard symbol instead of a search character. When searching, insert a tilde before the asterisk, as in 123~*789, and you’ll get only exact matches.

Fix problems comparing time values. When you try to compute a time value such as =TIME(12,0,0), meaning noon, with =NOW(), the comparison often comes out wrong, because NOW() includes the date as part of its numeric value. Compare instead with =NOW()-TODAY().

Display the results of date arithmetic in hours. When you add or subtract two date and time entries and format the sum as a number, Excel displays the result in fractions of a day, so 8 hours appears as 0.33. To display the result in hours, simply multiply the result by 24.

Define absolute and relative cell references. When you copy a formula in Excel, its cell references are automatically changed to match the location of the cell where you paste the formula. If part of your formula requires the value in a specific cell, and you don’t want that value to change when you copy the formula, assign the cell an absolute value by adding a dollar sign before the row letter and another dollar sign before the column number, as in$B$30.

Also read Learn to use appropriate “Cell Reference” in Excel Formulas

If you want only the row or column (but not both) to stay constant, just put the dollar sign before the corresponding part of the cell reference.

Shortcuts (More Shortcuts Here)

Handy Key Combinations for Excel:

F2: Edit a cell’s contents.

Ctrl-1: Open the Format Cells dialog.

Ctrl-‘: Copy formula from cell above into the current cell.

Ctrl-Shift-“: Copy value from cell above into the current cell.

Ctrl-R: Fill contents of active cell into selected cells to the right.

Ctrl-D: Same as above, but fills into selected cells down.

Ctrl-`: Toggle between showing cell values and formulas.

Ctrl-Shift-~: Format as general (no special number formatting).

Ctrl-Shift-!: Format as number.

Ctrl-Shift-$: Format as currency.

Ctrl-Shift-#: Format as date.

Ctrl-Shift-%: Format as percentage.

Alt-=: Autosum a range of cells.

For more, enter keyboard shortcuts in the Help field.

Also read Top 10 ways to Clean your Data in Microsoft Excel

---

Courtesy: pcmag.com