Replace formulas with values

Excel Tip #006

Replace formulas with values

This is one of the most useful tip that we use in everyday while we use excel. Consider you have a range of cells (or) formulas written. You want to copy the range and move it to other range of cells (or) in a new sheet.

This can be done in 2 ways - either by a keyboard shortcut (or) by a mouse shortcut. Let us consider the below range of values which are all formulas.
Range of cells with Formulas
The usual way of doing this is - copying the range, right click and then paste as values (shown in the below image).
Copy pasting as values - Usual method
Keyboard shortcut

After copying the range, press ALT + E + S which will pop up a window. Press V (denotes values) and enter, will result the range as values.
Copy pasting values - Keyboard Shortcut
Mouse shortcut
  1. Select all the cells that contain formulas
  2. Right click on the selection border (your mouse pointer changes to 4 sided arrow)
  3. Right click + Drag the selection a few cells away and drop them back at original place
  4. A menu appears. Select Copy here as values only
  5. Copy pasted as values, done

Comments

Post a Comment

Popular posts from this blog

VLOOKUP - Exact Match & Approximate Match

Image
Excel Tip #013 VLOOKUP - Exact Match & Approximate Match In the previous tip #012 (VLOOKUP & INDEX + MATCH), we learnt about VLOOKUP, it's syntax and how to use it. We also learnt about its drawback and how INDEX & MATCH can be helpful. Like said in the previous tip, there are 2 match option available in VLOOKUP - Exact & Approximate Match . Let us first refresh the syntax of VLOOKUP. Syntax: = VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) In simple terms, =  VLOOKUP (value/data that is used as reference, data table, the column # where our resultant data resides, exact (or) approximate match) Let's have a closer look at the last argument you supply to the Excel VLOOKUP function - range_lookup. You are likely to get different results in the same formula depending on whether you enter TRUE or FALSE (Approximate or Exact). If range_lookup is set to FALSE , the formula searches for exact match, i.e. for the lookup value exa...
Read more

Row 1 Is Missing. How to unhide?

Image
Excel Tip #030 Row 1 Is Missing. How to unhide? As you know, hiding and unhiding rows in Excel is simple as selecting them, right-clicking and choosing Hide or Unhide . The trick to selecting hidden rows, is to select a row above or below and then drag to included the hidden rows. For example, if you accidentally hide row 1 of your worksheet or inherit someone else's worksheet with one or more of the top rows hidden, you may be wondering how to unhide them since apparently you cannot select them. Row 1 Hidden There is a really easy solution. 1) Point to the row 2 header , press the left mouse button and drag upward to the top of the worksheet. Although you cannot see it, row 2 and row 1 are now selected 2) Right click anywhere on row 2 3) Choose Unhide from the right-click menu Unhiding hidden Row 1 NOTE : This also works for multiple hidden rows. There is another method of doing the same if Row 1 is hidden. 1) Go to the top left corner of the sheet abov...
Read more

Reduce file size and load/save time with .xlsb files

Image
Excel Tip #004 Reduce file size and load/save time with .xlsb files Using .xlsb files can improve performance if your spreadsheet does a lot of number-crunching. Excel has different options to save a file as an excel file - .xls, .xlsx, .xlsm and .xlsb. The .xls file format used by Excel 2003 and earlier is, internally, a "binary interchange file format"(called BIFF with various suffixes, e.g. BIFF11). Among its limitations, it can store up to 256 columns and 65536 rows. The .xlsx file format, default in Excel 2007, is really just a ZIP file with a bunch of XML files inside. It supports things like a million rows and multithreading optimizations for cell calculation order. This file type is typically smaller than an .xls file with the same contents and is probably faster to open in Excel 2007. The .xlsm file format is an Excel Macro-Enabled Workbook file. Just like with .xlsx files, Microsoft's .xlsm file format uses XML architecture and ZIP comp...
Read more