Excel Getting Cells to Show Up Again

Excel has fabricated our life easier in and then many ways. Nonetheless, at that place are some Excel Issues that continue to waste material our time and pump upward the frustration levels. These are modest bug that can be stock-still in a few seconds, but to not know the right way to do it can toll a lot of time.

24 Excel Tricks for Day-to-day Work

In this blog post, permit me unveil 24 Excel tricks that will save y'all a lot of time and free energy. If you piece of work with excel, sooner or afterwards you get into trouble with something that seems incommunicable.

Here are some quick fixes that will make it possible.

Excel Flim-flam #1 – Re-create-Paste Formulas without Changing References

It happens all the time. You endeavour to copy a cell with a formula to another cell and information technology gets all mixed up. This happens because the references change when you copy to some other location (unless of course you be strict and gear up all the references).

Here is the quick fix:

1. Select the cells that you want to copy.
2. Become to Home –> Number. In the Number format drop-downward, select Text. This volition alter the cell's format to Text.
3. Press F2 to go into the edit mode. Now concord the Control key and hit Enter.
4. Copy the cells.
5. Become to the destination cell and paste.
6. Change the format from Text to General.
7. Press F2 and Hit Control + Enter.
8. And You are Done!!.

Hither is another groovy method by Excel MVP Tom Urtis (using Notice and Replace) [credit to Dave Bruns of ExcelJet for showing me this method]

Excel Fob #two – Fill Handle Does Not Show Upwardly

I see then many people facing this effect. The Make full handle only does not evidence up for work.

Here is the fix:

1. Go to File –> Options.
2. In the Options Dialogue box, select Advanced.
3. Bank check the pick 'Enable Fill Handle and cell drag-and-drop'.

Excel Trick #3 – Number as Text

I get stuck with this a lot of times. Numbers are formatted every bit text and practise not work well in formulas.

Here is a quick fix:

• Add 0 to the cell. This volition keep the number intact and tin exist used in formulas.

A lot of people apply the apostrophe (') before the number to make numbers as text. This excel trick would piece of work in this example as well.

Excel Trick #4 – Need to Catechumen Formulas to Values

This is an easy one. If you have formulas that need to be converted to values, here are the steps:

1. Copy the cells.
2. Correct-click and select Paste Special.
3. In the Paste Special Dialogue Box, select Values (Keyboard Shortcut – Alt + Eastward + Southward + V).
4. Click OK.

          See Also: Larn more about Paste Special Shortcuts.

Excel Pull a fast one on #v – Cursor Stuck – Not Able to Modify Active Cell by Arrow Keys

The outset time this happened to me, I closed the Excel workbook and restarted my system. The problem, of course, can be solved hands without restarting.

The result, in this case, is that y'all have accidentally activated the Scroll Lock.

Switch it off and go going.

Excel Trick #vi – VLOOKUP/Match doesn't work even when there is a Match

You tin see that the 2 values match perfectly, simply the VLOOKUP or MATCH formula would still say there is no match.

Argghhhhh…

Extra spaces are the culprits here. If the text has leading or abaft spaces (or more than one infinite between words), excel would not consider it equally an exact lucifer, and your formula would throw up unexpected results.

The way around – use the TRIM function. It removes any leading and trailing spaces, and any extra infinite between words.

          Run into As well: A lot of bug tin can be solved if yous know how to make clean your data in excel.

Excel Play a joke on #7 – Want to Hide Text in Cells

Sometimes you may need to hide text/numbers in a prison cell so that the user can non see it.

There are 2 means to do this:

• The skilful 1:  Make the text font colour white (or any colour is the background).
• The better one: Select the prison cell and hit Control + 1. This opens the Format Cells dialogue box. In number tab, select Custom and blazon the format ;;;

Excel Pull a fast one on #8 – Demand to Copy Visible Cells Merely

• Question: What happens when you copy a range of cells which has hidden rows/columns, and paste it somewhere?
• Respond: All the prison cell values (Visible + Hidden) become copied and pasted.
• Concern: I don't want to copy hidden cell values.
• Solution:  Select the cells and press Alt +;(this selects visible cells only). Now paste anywhere and merely visible cells gets pasted.

Excel Trick #ix – Select All Blank Cells in One go

I oft import data from various databases. A lot of cells in this data are blank, where I have to insert a 0 or North/A. A quick mode to select all blank cells:

1. Select all the cells in the range.
2. Go to Dwelling house –> Editing –> Find and Select –> Go to Special.
3. Select Blanks in the Go To Special Dialogue box.
4. Click OK.

This will select all the blank cells. To enter 0 in all the cells, type 0 (this volition enter the value in the active cell) and printing Command + Enter.

Excel Play tricks #10 – Demand to Remove Line breaks from Text

If you have a lot of line breaks and you want to remove these line-breaks, doing it manually is going to have a lot of time.

Information technology would exist a shame if you have to do this manually.

Try this quick-fix:

1. Select the information
2. Become to Home –> Observe and Select –> Replace (Keyboard Shortcut –Command + H)
3. In the Discover and Replace Dialogue Box:
   • Discover What: Press Command + J (you may not meet anything except for a blinking dot)
   • Supplant With: Space bar graphic symbol (striking space bar one time)
4. Click on Replace All (make sure give-and-take wrap is enabled)

https://world wide web.youtube.com/watch?v=zj_OTwwt7Tg

          Encounter Too: Read more near astonishing things that Discover and Replace can practise for yous.

Excel Trick #xi – Sorting does not Work when Cells are Merged

Of course, information technology does not.

It wasn't fabricated that way. But that does not mean you can non accept what you desire. Hither is a substitute for Merge and Middle, and it is called Eye Beyond Pick.

Here is the right way to Merge cells in Excel:

1. Select the cells that you want to merge (columns only. For rows, become ahead and use merge and center).
2. Press Command + 1 to open up Format Cells dialogue box.
3. Become to Alignment Tab.
4. In Horizontal drop-downward, select Eye Across Selection.
5. Click OK.

At present yous take something that looks the same, and works keen with Sorting.

Excel Fob #12 – Data has Duplicates

There tin can be 2 things you can practise with duplicate data – Highlight It or Delete It

• Highlight Duplicate Data: Select the data and Go to Home –> Conditional Formatting –> Highlight Cells Rules –> Duplicate Values.
  • Specify the formatting and all the indistinguishable values get highlighted.
• Delete Duplicates in Data: Select the information and Go to Data –> Remove Duplicates.
  • Select if your data has headers, then select the column and click OK. This removes duplicates from the list. If you desire the original list intact, re-create-paste the data at another location so do this.

                      Related          : The Ultimate Guide to Find and Remove Duplicates in Excel

Excel Trick #13 – Demand to Check for Spelling Errors

Press F7 to run spell-check.

Here is a detailed tutorial on using Spell check in Excel.

Excel Trick #xiv – Scrolling Down on Large Data Sets hide Column Titles

There are 2 means to resolve this.

• Use Excel Table Characteristic: Using Excel Table feature ensures that when you roll down, the headers are e'er visible. Every bit a expert exercise, Excel Table should e'er exist used with tabular data as information technology has some very powerful and useful feature. Learn more than nearly Excel Tables
• Using Free Panes Options: If y'all want more than the header to be visible (say in instance of a sub-heading or values such as total, boilerplate at the starting time), use the Excel Freeze Pane feature. To exercise this:
  • Select the Entire row in a higher place which you want to Freeze the Rows (or the leftmost cell in that row)
  • Go to View –> Freeze Panes –> Freeze Panes
    This volition enable you to always see the fixed rows when you scroll down.

Excel Trick #15 – Wasting fourth dimension Scrolling Back and Forth in Large Information Sets

If you lot work with huge information sets and you lot have to roll upwards and down (or right and left) to refer to some data points, in that location is a way you can salvage a lot of time and scrolling – by using the Split Window feature.

This feature tin can separate your window in two or four parts, and each part has its own scrolling enabled. And then if you have to refer to data in say column CZ, while you are working in column A, then this feature is perfect for you.

1. Go to View –> Window –> Separate.

This splits the worksheet into four parts. Each part tin be controlled past an additional scroll bar (vertical and horizontal). If you lot only desire a vertical separate, drag the horizontal split line to the bottom (and it will disappear).

Excel Play a trick on #16 – Debugging Formula

Missed a comma, wrong reference, missing argument, wrong parenthesis position and and so on.. There could be hundreds of reasons for a wrong result by a formula.

Debugging a formula could exist painful, especially if it has been created by someone else. Here is a way to go far like shooting fish in a barrel and simple. To debug a formula:

1. Select the cell that has the formula.
2. Become to Formulas –> Formula Auditing –> Evaluate Formula (Keyboards Shortcut Alt + TUF).
3. Click on Evaluate to see the steps the formula is evaluated by Excel.

Want to debug part of a formula – Here is another fashion using the F9 key

Excel Trick #17 – Excel Inserts Cell Reference While Editing a Formula

This is past far one of the about irritating excel issues that I get through almost daily. Imagine you take a huge formula, you press F2 to edit the formula (in a cell or conditional formatting or named range), and as soon every bit you press left key to edit function of it, Excel inserts some unwanted jail cell reference. Excel does this sometimes as it does not know that you are in Edit Mode. You can see the electric current way at the bottom left of your screen.

The way effectually is simple, press F2 once to get into the edit mode, and at present you can roam around freely in your formula.

Excel Trick #18 – Too many named ranges (do not call up the names)

I use Named Ranges a lot. It has many benefits and it's easy to utilise. But what if in that location are also many Named Ranges?? Would you have to go back to the name manager again and again to get the proper noun?

NO!!

Simply use the shortcut F3. Information technology will display the names of all the named ranges in your workbook. Just double-click on the one you want to apply and information technology will be pasted (in the formula if you making one, or in the sheet)

Question for You? What happens if yous have many named ranges, and you lot decrease the zoom below twoscore%? I will get out that for y'all to effigy out.

Excel Trick #nineteen – Fault Mistake Everywhere

An error is ane ugly blemish on your spreadsheet. Only sometimes, errors are the necessary evil. Yous may want your formula to render an error (say when a lookup value in not constitute in VLOOKUP). But yous don't have to get through the travails of staring at one. Simply utilize IFERROR .

IFERROR would not work for Excel version 2003 and earlier. In that case, use a combination of IF and ISERROR.

Excel Pull a fast one on #20 – Demand to Quickly Delete All Comments

I often re-use templates that have comments. To remove all the comments is ane of the worksheet operations that I practise whenever I commencement over. Hither is a quick style to select all the comments at i become and delete all:

1. Select all the cells.
2. Become to Home –> Editing –> Find and Select –> Go to Special.
3. Select Comments in the Become To Special Dialogue box.
4. Click OK.

This will select all the cells that have comments in it. Now go to any of the selected cells, right click and select Delete Annotate

Excel Play a joke on #21 –  Switch Between Excel Spreadsheets Only

Are you lot one of those who open up zillions of applications at the aforementioned time.

I oftentimes take Google Chrome, Excel, PowerPoint, Kindle, Mozilla Firefox, Email, and many more applications open up at the same time.

Near of us use Alt + Tab to cycle through different open applications, but what if y'all want to cycle through open Excel Workbooks only. Employ Control + Tab instead.

Excel Trick #22 – Display Number with Text

Want to display the number 100 as 100 Million or 100 Grams? You lot can exercise this, and still use this number in calculations. The play a joke on is in knowing the correct number format. To display 100 equally 100 1000000:

1. Get the cell that has the numbers (or select all the cells where you want to employ this).
2. Printing Command + ane.
3. In the Number tab go to Custom (in the left pane).
4. Alter General to Full general "Meg".

Custom Number formatting is a delight in itself. Acquire some amazing play a trick on using Custom Number Format

Excel Play a joke on #23 – Quickly Scroll to the Right in a Huge Data Set up

Suppose yous have a huge data set that covers a lot of columns (say 200 columns). And you have to scroll through your data oftentimes. What are the ways you can practice this.

• Usual manner ane: Utilize the right arrow key (and cover each column one by one).
• Usual Mode 2: Leave the keyboard, get hold of the mouse, select the scroll bar and curl.
• Here is the third not-so-usual way – Apply Alt + PageDown Fundamental. It does what Folio down does for rows. It jumps 20 columns at a time.

Excel Play a joke on #24 – Suffering from Slow Spreadsheet

We have all been through this. Excel Spreadsheets have a tendency to get too damn boring.

Some steps below may assist things speed-up. [I wrote a more detailed commodity on how to speed up spreadsheets. Click here to read]

• Use Helper columns.
• Avoid Volatile formula.
• Utilise Manual mode of calculation instead of automatic.
• Use Excel Tables for housing data.
• Avert assortment formula.

These are my height 24 Excel tricks (in no specific order). I am sure there are hundreds more, merely there is simply so much a person tin write at one become!! 😉

Share your listing of elevation daily troubleshooting tricks you lot use to become the work done 🙂

Useful Excel Resource:

• Useful Excel Interview Questions + Answers.
• Free Online Excel Training.
• Gratuitous Excel Templates.
• How to Copy Chart Formatting in Excel

Other Excel tutorials you may also like:

• Copy and Paste Multiple Cells in Excel (Adjacent & Non-Adjacent)

leewhiseas.blogspot.com

Source: https://trumpexcel.com/24-excel-tricks/

Share this post