• Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us
Excel

2 Simple Ways to Convert Formulas to Values in Excel

0 Comments

2 Simple Ways to Convert Formulas to Values in Excel: In today’s fast-paced world, Excel has become an indispensable tool for data analysis and management. From basic calculations to complex financial models, Excel offers a range of functions and formulas that help users manipulate data with ease. However, sometimes it’s necessary to convert formulas into values to prevent changes to data or to hide formulas from others. In this article, we’ll explore two simple ways to convert formulas into values in Excel.

Method 1: Using the Paste Special feature

To convert formulas to values in Excel, there are several ways. The simplest way is to use Paste Special. To do this, follow these steps:

Step 1: Select the data range you want to convert. Press CTRL + C to copy this data range.

2 Simple Ways to Convert Formulas to Values in Excel

2 Simple Ways to Convert Formulas to Values in Excel

Step 2: In the place where the data is pasted, right-click and select Paste Special, then select the Paste Values icon. Or you can directly select the Paste Values icon below the Paste Options section as illustrated below.

2 Simple Ways to Convert Formulas to Values in Excel

2 Simple Ways to Convert Formulas to Values in Excel

Immediately after that, the data range containing the formula has been copied to another data range without being referenced by the formula, so it still retains its value.

2 Simple Ways to Convert Formulas to Values in Excel

2 Simple Ways to Convert Formulas to Values in Excel

Method 2: Using VBA

The second way to convert formulas to values in Excel is to use VBA. To do this, follow these steps:

Step 1: On the Developer tab of the ribbon, select Visual Basic. The VBA window will appear.

2 Simple Ways to Convert Formulas to Values in Excel

2 Simple Ways to Convert Formulas to Values in Excel

Step 2: In the Microsoft VBA window, select Insert. Then select Module.

2 Simple Ways to Convert Formulas to Values in Excel

2 Simple Ways to Convert Formulas to Values in Excel

Step 3: Copy the code below into the Module.

Sub DisplayedToActual()

‘Updateby20131126

Dim Rng As Range

Dim WorkRng As Range

On Error Resume Next

xTitleId = “CONVERT”

Set WorkRng = Application.Selection

Set WorkRng = Application.InputBox(“Range”, xTitleId, WorkRng.Address, Type:=8)

Application.ScreenUpdating = False

For Each Rng In WorkRng

Rng.Value = Rng.Text

Next

Application.ScreenUpdating = True

End Sub

2 Simple Ways to Convert Formulas to Values in Excel

2 Simple Ways to Convert Formulas to Values in Excel

Step 4: Press F5 to run the code. The CONVERT dialog box will appear. You select the data range to be converted and then press OK to complete.

2 Simple Ways to Convert Formulas to Values in Excel

2 Simple Ways to Convert Formulas to Values in Excel

The result is that all cells containing formulas have been completely converted to values.

 

2 Simple Ways to Convert Formulas to Values in Excel

2 Simple Ways to Convert Formulas to Values in Excel

In conclusion, Excel provides a wide range of tools to manage and analyze data, and the ability to convert formulas into values is an important function for many users. Whether you prefer using Paste Special or VBA, knowing how to convert formulas into values can save time and help you achieve your goals more efficiently. We hope this article has been helpful in providing you with valuable information and insights.

10
75 Views
Where to Download Sound for PowerPoint Slide Design?PrevWhere to Download Sound for PowerPoint Slide Design?March 13, 2023
Using VLOOKUP Nested in VLOOKUP Function in ExcelMarch 13, 2023Using VLOOKUP Nested in VLOOKUP Function in ExcelNext

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts
  • Retrieve Data Based on Data Validation
    September 7, 2023
    Excel
  • How to Convert Multiple Rows to Columns and Rows in Excel?
    September 6, 2023
    Excel
  • VLOOKUP for Automatic Discount in Data Validation
    September 6, 2023
    Excel
  • How to Convert Text Dates to Dates in Excel?
    September 5, 2023
    Excel
Categories
  • Blog 80
  • Excel 754
  • Powerpoint 36
  • Software tricks/tips 128
  • Uncategorized 8
  • Word 109
Tags
the ISERROR
Top rated products
  • AVG Internet Security 2021 1 Device 1 Year Global AVG Internet Security 2021 1 Device 1 Year Global
    Rated 5.00 out of 5
    $11.00
  • Buy Office 2021 Professional Plus Key Global For 5 PC
    Rated 5.00 out of 5
    $45.00
  • Avast SecureLine VPN 2021 2 Years 5 Devices Global Avast SecureLine VPN 2021 2 Years 5 Devices Global
    Rated 5.00 out of 5
    $47.00
  • Windows 11 Pro Product Activation Key - Latest version 2021
    Rated 5.00 out of 5
    $11.00
  • AVG Internet Security 2021 10 Devices 1 Year Global AVG Internet Security 2021 10 Devices 1 Year Global
    Rated 5.00 out of 5
    $30.00
Products
  • Buy Office 2021 Professional Plus Key Global For 5 PC
    Rated 5.00 out of 5
    $45.00
  • Buy Office 2021 Professional Plus Key Global Bind To Your Microsoft Account
    Rated 4.83 out of 5
    $49.00
  • Windows Server 2022 Datacenter Key Global
    Rated 5.00 out of 5
    $15.00
  • Windows Server 2022 Standard Key Global
    Rated 4.47 out of 5
    $15.00
  • Windows Server 2022 Remote Desktop Services Device Connections (50) Cal Key Global
    Rated 5.00 out of 5
    $45.00

Buffcom.net always brings the best digital products and services to you. Specializing in Office Software and online marketing services

BIG SALE 50% IN MAY

Microsoft Office
Microsoft Windows
Anti-Virus
Contact Us

Visit Us:

125 Division St, New York, NY 10002, USA

Mail Us:

buffcom.net@gmail.com

TERMS & CONDITIONS | PAYMENT GUIDE  | SHIPPING POLICY  | REFUND POLICY

Copyright © 2019 buffcom.net  All Rights Reserved.