Excel offers over 450 pre-designed formulas, streamlining complex tasks. However, users often find the dollar sign ($) puzzling yet useful for certain functions. This blog post explains the role of the "$" in Excel, helping you unlock another layer of the software's capabilities and how you can use “$” function in Excel.
What is the $ Function in Excel?
The "$" sign in Excel has two main purposes that are quite common.
Locking References: If you put the "$" sign before the column letter and the row number (like $A$1), it makes an absolute cell reference. It keeps the referred cell unchanged when you copy or move a formula to different cells. It's like a fixed point that helps you keep your calculations accurate when working with various cells.
Currency Display: You often see the "$" sign in Excel to show currency values. Adding the "$" symbol before a number (such as $100) changes the cell content to a currency format. That makes it simple to show money values consistently in your spreadsheets, making your data look neat and easy to understand.
Absolute cell reference with $ Symbol
An absolute cell reference with the "$" symbol is a way to lock down a specific cell's column and row when creating a formula in Excel. By placing the "$" sign before the column letter and row number (for example, $B$3), you're telling Excel to keep that particular cell's location fixed, even if you copy or move the formula to other cells.
For Example, you have a simple Excel table with sales data. You want to calculate the total sales for each product while referring to a fixed sales tax rate.
In an empty cell, you want to calculate the total sales for "Product A", including the sales tax.
Start typing your formula: =Sales_Amount * (1 + Sales_Tax_Rate).
When you reach the Sales_Amount cell reference, press F2 or double-click the cell to enter the formula editing mode.
Now, insert the "$" sign before both the column letter and the row number of the Sales_Tax_Rate cell reference, like this: $B$2.
Press Enter to finalise the formula.
Copy the formula cell and paste it below to calculate totals for other products.
Placing the "$" sign before $B$2 ensures that the Sales Tax Rate cell reference remains fixed as you copy the formula for other products.
This means that the tax rate won't change, allowing you to accurately calculate the total sales for each product, including the consistent tax rate.
$ Dollar Sign
The "$" sign in Excel serves multiple functions, including currency formatting and cell referencing. You can manually add it or use the F4 shortcut for cell references, and apply it through formatting for currency display.
Method 1: Using the F4 Key
Steps to Create an Absolute Reference with F4 Key in Excel:
Step 1: Open Excel and go to the worksheet with your data and formulas.
Step 2: Navigate to the cell where you want to enter or edit a formula with a cell reference.
Step 3: Instead of typing the dollar signs, press the F4 key. It will automatically insert both dollar signs around the cell reference.
Step 4: Press F4 again if you want to switch between different reference types, which can help you lock rows or columns as needed.
Step 5: Double-click the fill handle (the small square at the bottom-right corner of the active cell) to copy the formula to nearby cells. This ensures the absolute reference works correctly.
Step 6: Always check the formula bar to confirm that the formula is copied accurately.
Using the F4 key, you can conveniently create and manage absolute references in your Excel formulas without manually typing the dollar signs.
Method 2: Manually Create an Absolute Reference with $ Sign
Step 1: Open your Excel worksheet and locate the cell where you want to input a formula with an absolute reference.
Step 2: Begin typing your formula, and when you reach the point where you need to insert the cell reference, stop typing momentarily.
Step 3: To create an absolute reference for the entire cell reference, insert the $ sign before the column letter and the row number, like this: $C$5.
Step 4: Press Enter to finalise your formula.
Steps to Use $ Sign for Mixed Reference
Step 1: In a formula, if you want to fix either the column or row while allowing the other to change, insert the $ sign before the part you want to lock. For example, to lock the column but not the row, use $C5.
Step 2: To lock the row but not the column, use C$5.
How to Use $ Function in WPS Excel Spreadsheet?
Absolute cell reference
An absolute cell reference in Excel locks a cell's address in a formula, preventing it from changing when the formula is copied to other cells. We'll explore various methods with examples.
Method 1: Manually Adding $ Symbol
Step 1: Launch WPS Excel and open a worksheet containing your data and formulas.
Step 2: Navigate to the cell where you want to create a formula with an absolute reference.
Step 3: Begin typing your formula and stop at the point where you want to insert the cell reference.
Step 4: To make the reference absolute, manually add the $ sign before the column letter and row number—for example, $B$3.
Step 5: Complete your formula and press Enter to apply it.
Method 2: Using the F4 Shortcut
Step 1: Open your WPS Excel spreadsheet and input your data.
Step 2: Select the cell where you want to enter or edit a formula with an absolute reference.
Step 3: Begin typing your formula, and when you reach the cell reference part, follow these steps:
Step 4: Instead of typing the $ sign in WPS spreadsheet manually, press the F4 key. This automatically inserts both $ signs around the cell reference, creating an absolute reference.
Step 5: Press F4 again to toggle between reference types, locking rows or columns as needed.
Step 6: Press Enter to finalise the formula.
Method 3: Locking Only Column or Row
Step 1: Open your WPS Excel spreadsheet.
Step 2: Find the cell where you'll type your formula.
Step 3: Begin typing your formula and pause at the cell reference.
Step 4: To lock only the column, use the $A1 format. To lock only the row, use A$1.
Step 5: Finish typing your formula and press Enter.
Example: Using the $ Symbol for Absolute References
Let's consider a simple sales table:
Example 1: Total Sales Calculation
You want to calculate the total sales for Product A. Let's use the manual method.
In a new cell, type: =Price * Quantity.
When you reach Price, manually type $ before both column and row: $B$2.
Press Enter.
Example 2: Calculate the Total for All Products
Suppose you want to calculate total sales for all products. We'll use the F4 key.
In a new cell, type: =Price * Quantity.
When you reach Price, press F4 to insert $B$2.
Double-click the fill handle to copy the formula for Product B's total.
The formula adjusts automatically due to the absolute reference.
The $ Sign
Several ways exist to utilise the "$" dollar sign in WPS Spreadsheet. These methods allow you to create absolute references, format currencies, and enhance your calculations.
Method 1: Using the F4 Key
Launch WPS Spreadsheet and open the worksheet containing your data and formulas.
Locate the cell where you need to input or modify a formula requiring a cell reference.
Instead of manually adding the "$" sign, press the F4 key to automatically insert it before the column letter and row number, such as "$D$2".
Press F4 again to cycle through different reference types for locking rows or columns.
Double-click the fill handle to copy the formula to adjacent cells, ensuring the absolute reference remains accurate.
Check the formula bar to verify the accurate duplication of the formula.
Method 2: Using the CONCATENATE Function
Understand the purpose of the CONCATENATE function, which combines text in formulas.
Formulate the CONCATENATE function to incorporate the "$" sign with dynamic cell references.
For instance, in a currency conversion scenario, use the CONCATENATE function to join the "$" sign with the exchange rate cell reference, creating a fixed reference.
Apply the formula with the CONCATENATE function for consistent conversions.
Suppose you have a table of expenses that you want to display in currency format with the "$" sign. Here's how to use the CONCATENATE function to achieve this:
In a new cell (say B2), you want to display the expense amount with the "$" sign.
Use the CONCATENATE function to join the "$" sign and the amount from cell B2. The formula would be: =CONCATENATE("$", B2).
Press Enter to apply the formula.
Using the CONCATENATE function, this formula combines the "$" sign as a text string with the value in cell B2. The resulting cell will display "$150" for the "Groceries" expense.
Method 3: Typing the "$" Dollar Sign
Identify the cell reference within your formula that requires the "$" dollar sign for an absolute reference.
Type the "$" sign directly before the cell reference's column letter and row number.
For example, type the "$" sign before the column and row identifiers in a loan payment calculation to lock the interest rate reference.
It ensures the reference remains unchanged when copying the formula to other cells.
How to Download WPS Spreadsheet?
WPS Spreadsheet is a powerful tool for creating and managing spreadsheets. Follow these steps to download and install WPS Spreadsheet on your computer:
Step 1: Access the WPS Office Website
1. Open your preferred web browser.
2. Go to the official WPS Office website. You can search for "WPS Office" or visit "www.wps.com."
Step 2: Navigate to the Downloads Section
On the WPS Office website, navigate to the "Downloads" section. It is usually located in the top menu or as a prominent button on the homepage.
Step 3: Choose the Appropriate Version
Once in the "Downloads" section, you'll find various versions of WPS Office, including those for Windows, macOS, Android, and iOS.
Select the version corresponding to your operating system (Windows, macOS, Linux, etc.). Click on the respective download button.
Step 4: Download the Installer
After clicking the download button, a file named "WPS_Office_Installer.exe" (for Windows) or a similar file for your operating system will begin downloading.
The download time depends on your internet speed.
Step 5: Run the Installer.
Once the download is complete, locate the downloaded installer file on your computer.
Double-click the installer file to run it.
Step 6: Install WPS Spreadsheet
The installer will launch the setup wizard.
Follow the on-screen instructions to install WPS Office.
You may be prompted to choose the components using the CONCATENATE function to install. Make sure "Spreadsheet" or "WPS Spreadsheet" is selected.
Select the installation directory and any other preferences as needed.
Click "Install" or "Next" to proceed.
Step 7: Finish Installation
The installer will begin installing WPS Spreadsheet.
Once the installation is complete, you'll likely see a confirmation screen. Click "Finish" or "Done."
Step 8: Launch WPS Spreadsheet
After installation, locate the WPS Spreadsheet icon on your desktop or your applications folder.
Double-click the icon to launch WPS Spreadsheet.
Step 9: Activate the Software (If Required).
Depending on your downloaded version, you might need to activate the software using your WPS account or a license key. Follow the provided instructions to activate the software.
Why Choose WPS Office?
WPS Office is a feature-rich suite for handling documents, spreadsheets, and presentations, serving as a powerful alternative to other office software. Its free services make it appealing for both personal and professional use.
Key Reasons and Features to Choose WPS Office
Price Advantage
Document Compatibility
Cross-Platform Convenience
Free Cloud Services
Language Support
FAQs
How do I change the currency symbol in the WPS spreadsheet?
Launch WPS Office and open your document.
Insert Euro Symbol: Click "Insert" > "Special Characters" > "Currency Symbols" > Select Euro symbol (€) > Click "Insert."
Verify and Close: Check Euro symbol insertion > Close Special Characters dialogue box.
How do I format a number in WPS Excel?
Select Cells: Click on the cell or cells with the number you want to format.
Format Cells: Right-click and choose "Format Cells" or go to "Home" > "Number Format" dropdown.
Choose Format: In the "Number" tab, pick a category (like "Currency" or "Date") and a format style.
Apply: Click "OK" to see your number instantly formatted the way you selected.
Summary
This article offers a comprehensive guide to using the "$" function in WPS Spreadsheet for absolute cell referencing and currency display. WPS Spreadsheet has two referencing types: absolute with "$" for fixed cells and mixed for locked rows or columns. The tutorial explains $ function use. The step-by-step guide also explains why WPS Office is an affordable, feature-rich, cross-platform office suite and ideal for various user needs along with a complete guide on how to download and edit your WPS Spreadsheet.
