Reference format and value from another cell in excel Is it possible to retain that value in the referencing cells if I e In this tutorial, I will show you two really simple ways to copy conditional formatting from one cell to another in Excel. The range where the lookup value is located. Jun 23, 2024 · How to Perform Conditional Formatting Based On Another Cell in Excel is done by using the AND, OR, SEARCH functions, and AVERAGE function. Or you can use VBA to references cells and their formatting. Sep 3, 2025 · Learn how to use Conditional Formatting in Excel to format cells based on another cell’s value. Download the practice workbook. Apr 15, 2024 · The agenda for today’s tutorial is to show you the easy & basic excel formula to copy cell value to another cell in 5 suitable ways. Learn all about the Absolute, Relative, and Mixed cell references in Excel and how to use these in formulas. Apr 30, 2025 · Learn how to format cells in Excel based on another cell's value using conditional formatting with simple formulas and step-by-step examples. Press F4 to switch between the reference types. If you want to prevent Excel from changing the cell references when copying cells, you need to use absolute references. Cell references in Excel are very important. We used Paste Special command and VBA to explain this. 57 instead of 8. The formula =A2 > 4 has the cell A2 hardcoded, yet it magically works when applied to cells A6, A7, etc. For example, if your lookup value is in cell C2 then your range should start with C. In Excel, you can use conditional formatting based on another cell. Jul 10, 2019 · Cross referencing cells and ranges in other sheets and different workbooks in Excel can be daunting at first. Mar 1, 2017 · When I reference this cell, say in cell B1: =A1 In B1 I just get a long string of text that has none of the formatting that is present on A1 Is there a way to reference and keep the formatting? I can use format painter and it will recognise the line breaks within the cell, but aspects like the partially bold writing are still not recognised. To create an absolute reference, insert a dollar sign ($) before Feb 6, 2024 · Conditional Formatting based on another cell value Hello! I'm working on a spreadsheet for work and would like to format a column to say "Already Done" if the value in a Cell on that line has an X or something similar. Sep 22, 2025 · Pull Data From Another Sheet Using Power Query Power Query is another cool database querying tool of Excel that lets you import data from different worksheets of the same workbook or from a different workbook. When using the A1 reference style, you would refer to any sale by first specifying the column alphabet Aug 18, 2025 · In Excel, applying conditional formatting based on another cell range means we format our selected cells using the values from other cells. Nov 16, 2025 · Learn how to apply conditional formatting based on dates in another cell, including highlighting before, after, equal & row-based date rules. When using lookup formulas in Excel (such as VLOOKUP, XLOOKUP, or INDEX/MATCH), the intent is to find the matching value and get that value (or a corresponding value in the same row/column) as the result. This tutorial covers step-by-step how to apply rules, write formulas, use relative vs. From simple color changes to complex formula-based rules, mastering conditional formatting can significantly improve Discover the difference between relative and absolute cell references in Microsoft Excel, and learn when to use relative and absolute cell references in Excel formulas. Conditional formatting in Microsoft Excel is a powerful tool that enhances the readability and analysis of data by automatically applying formatting based on cell values. Feb 15, 2023 · The Excel CELL function returns information about a cell in a worksheet. How can I make a link that picks up BOTH the content + the format. Mixed references have a combination of absolute and relative components. This guide includes practical examples and formulas to improve your spreadsheets. Is there any way to maintain formatting of the original cell in a linked cell? Example: Aug 13, 2024 · 2 methods are shown on how to copy conditional formatting with relative cell references in Excel including Format Painter and Paste Special. one char underlined, another word in bold. Aug 18, 2025 · This becomes more interesting when you want to apply formatting based on multiple values in that other cell. Read more. May 1, 2024 · When working with formulas in Microsoft Excel, a common issue is that the formatting of a cell can change after a formula is applied. For example, you may have a cell formatted as currency, but when you enter a formula, the formatting changes to a plain number. This is a great way to quickly and easily create dynamic spreadsheets. See below for a full list of information available. Mar 16, 2023 · The tutorial explains the basics of a cell reference in Excel, shows how to make absolute and relative references, refer to a cell in another worksheet and workbook, and more. If you want to apply conditional formatting based on the value of another cell including text or number or date etc. In the example shown, the formula used to apply conditional formatting to the range D5:D14 is: =$D5>$C5 This highlights values in D5:D14 that are greater than C5:C14. Jul 6, 2024 · You can leave cell blank if there is no data by using functions like IF, ISBLANK, and ISNUMBER by combining a small VBA macro code in Excel. Cell references in Excel enable users to refer to cells or cell ranges in a formula and use the data or values present in the specific cells for completing the calculations. Nov 6, 2023 · This tutorial demonstrates how to use the Excel INDIRECT Function in Excel and Google Sheets to create a cell reference from text. Nov 16, 2025 · Learn how to set conditional formatting rules for a column based on another column in Excel using formulas for numbers & text conditions. When you reference a cell or range of cells in another Excel sheet, you create a formula that points to a cell or range of cells in another sheet. Learn them, download the workbook and practice. Jul 18, 2016 · Have you tried using conditional formatting as below? I simplified the formula in the example but should work for your case as well. Enjoy learning with us! Oct 10, 2015 · I'm having a problem in MS-Excel. One can change cell references and cross-reference Excel cells from another worksheet or even a different workbook. The type of information to be returned is specified as info_type. absolute references, and create dynamic dashboards without any coding. Jun 15, 2024 · We discussed three methods to reference another sheet in Excel. A1 vs. Aug 29, 2024 · Three ways to reference cells: absolute, relative, and mixed references. Relative references adjust based on the new location. Mar 21, 2023 · See how to create external reference in Excel: reference another sheet, refer to a range of cells or defined name in a different workbook. Jul 12, 2024 · Learn how to easily reuse cell values in Excel. We can also apply conditional formatting to highlight an entire column based on another column with some easy steps. This feature allows users to quickly identify trends, anomalies, and specific conditions in a spreadsheet. Apr 26, 2024 · 0 I have a cell containing a text string with a specific format, e. Mar 4, 2021 · How do I use the value stored in C1 in other cells to get the path from C1 and add the tab name and the cell so I can reference the value in that file? I tried using: Jul 15, 2024 · Learn how to apply conditional formatting color scale based on another cell in Excel. So, I want to type in cell references: in cell B4, I'd type in C5, and in H4 I'd type in C8. Jul 15, 2024 · How to Reference a Cell in Excel Referencing a cell in Excel is a fundamental skill that allows you to pull data from one cell to another. Aug 8, 2017 · As near as I can tell, it's impossible to copy conditional formatting from one cell to another — by copy-paste, or paste special, or format painter — and have the pasted conditional formatting references the relative cells rather than the original cells. Apart from the basic referencing on the same sheet, the major part of this tutorial would be about how to reference another sheet or workbook in Excel Aug 19, 2025 · To reference cells in another sheet dynamically, you need to follow the steps below: Make a new sheet with three columns for the worksheet name, the cell number of the specific cell, and the resultant value. The value you want to look up, also called the lookup value. Nov 12, 2019 · Discover how to format an entire row of cells based on the value of another cell using conditional formatting that evaluates criteria. A1 and R1C1 are the two cell reference styles. It shows the function name, syntax format, argument details, and return value characteristics. The first is just a header row and a single column of item names. Cell Nov 15, 2025 · Learn how to use Conditional Formatting formulas in Excel to highlight cells containing specific, partial or multiple text values. It seems to me more practical for large ranges than Format Painter. When pulling data from Excel files using Power Query, you can make the process more efficient by using named ranges. Download practice book with formulas for free! Jun 9, 2022 · When creating your conditional formatting, set the range to which it applies to what you want (the whole sheet), then enter a formula (remove the $ signs) as if you were only formatting the upper-left corner. You’ll need to know the cell’s address, like A1 or B2, and use it in the formula bar. Jul 9, 2022 · This tutorial will demonstrate how to highlight cells based on a date in another cell using Conditional Formatting in Excel and Google Sheets. Enables reporting at different levels of significant. If I entered a value in sheet1 cell A1, that value appears everywhere that cell is referenced. Fortunately, there are several ways to keep the original formatting when using formulas in Excel. In this tutorial, I will show you two really simple ways to copy conditional formatting from one cell to another in Excel. In this section, you’ll explore how to apply the conditional formatting feature, considering either the date in a specific cell or the cells between two dates. Jul 30, 2023 · This tutorial will demonstrate how to highlight cells based on another cell value using Conditional Formatting in Excel and Google Sheets. There are no Excel functions that deal with formatting a cell or using the formatting of another cell. Dec 2, 2020 · How to format a cell based on another cell value in Excel? Ask Question Asked 4 years, 11 months ago Modified 4 years, 11 months ago If you want to apply conditional formatting based on the value of another cell including text or number or date etc. May 14, 2024 · In Excel, conditional formatting is used to highlight any cells based on predetermined criteria and the value of those cells. However, by using your own formula, you have more flexibility and control. Just use that--no advanced formula required. For a simple example: In cell A1 I have this: COUNT(B4:H4) Instead of choosing the range when I set up the formula, I'd like this range to be dynamic, based on the value in other cells. Apr 16, 2024 · Here we'll see how Excel data validation based on another cell is created. But in some cases, instead of getting the value, you may want the formula to return the cell address of the value. Conditional formatting allows you to apply a format to a cell based on the value in it. Understand the difference between relative, absolute and mixed reference, and you are on your way to success. CELL can get things like address and filename, as well as detailed info about the formatting used in the cell. For example, if the value of Mar 22, 2023 · Relative column and relative row This reference type is used in Excel conditional formatting rules least of all. But in some cases, you may want to apply conditional formatting to a cell or column based on values in another column. A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. Apr 4, 2025 · This table breaks down the essential components of the INDIRECT function. (Source) You could use conditional formatting to display a solid color or gradient fill in the alternate cells. Jul 6, 2024 · How to apply Conditional Formatting based on multiple values of another cell in Excel. When you reference that cell in a formula, it will use the underlying value. To change the type of cell reference: Select the cell that contains the formula. Conditional formatting in Excel allows you to format cells based on the values in the cells. I would like to format the cell background color in the first column of sheet 1 based on the values in the second column of sheet 2. Jul 5, 2024 · In this article, I have provided 3 ways to apply conditional formatting to the cells based on the date in another cell in Excel. Follow this simple guide to learn how to do it. Jan 4, 2025 · Learn how to use Excel conditional formatting based on another cell to highlight data. It is crucial in creating formulas, and there are three types: Absolute ($) references stay fixed when copied. Aug 9, 2024 · This article shows how to dynamically reference cell in another Excel sheet using INDIRECT, INDEX-MATCH, VLOOKUP functions to refer to a cell. Navigate to the destination sheet, right-click the target cell, and choose Paste Special > All. Can I use a function to reference a cells value and its formats? To specifically answer your last question on whether you can reference a cell's format, the answer is yes, by using the CELL function in your formula. I’ll even show you if the date is in another column. Excel uses this formula in relation to the first cell in the range, so if you wanted to Jan 16, 2009 · I have a workbook with two sheets. These functions help you to work with arrays of data, and are particularly useful when you need to cross reference between different data sets. This referencing enables you to consolidate data, perform calculations across multiple sheets, and maintain dynamic links that automatically update when the referenced data changes. In this article, you’ll learn how to apply conditional formatting rules in Excel where formatting depends on multiple possible values in a different cell. Jun 12, 2020 · To apply conditional formatting based on a value in another column, you can create a rule based on a simple formula. Jun 12, 2016 · However, the formula of E1 (which is C1*D1) gives 8. 55, I figured out that Excel uses C1's formula instead of the displayed value, which is not my expectation! So how can I enforce Excel to use the value (displayed) of a cell instead of its formula, to calculate another formula? Aug 24, 2023 · Examples of Excel conditional formatting with formula show how highlight cells and entire rows based on the values you specify or based on another cell's value. =CELL("format", A1) The values that this function returns are listed on this page: Sep 9, 2025 · To preserve the value, format, and note, we recommend using the Copy > Paste Special method: Go to the source sheet and select the desired cell. However, a couple workarounds/alternate methods: You could overlay a bar chart, removing all the backgrounds, borders, axes and gridlines, and setting the bar colour to be 60%+ transparent. Mar 15, 2023 · In order to make accurate calculations in Excel, it’s essential to understand how the different types of cell references work. Jun 10, 2025 · Learn how to use Excel conditional formatting based on another cell to create dynamic, data-driven visualizations for financial analysis and decision-making. May 21, 2024 · In this tutorial, we have shown 5 excel formula to copy cell value & format using format painter, VBA, paste, shortcut key and paste special. Mar 31, 2025 · This tutorial covers everything you need to know about Excel cell references —what a cell address is, how to use absolute and relative references, and how to reference cells across different sheets and workbooks. This guide will teach you all these and much more. How do i get conditional formatting to change to the new cell value I'm pasting to? Aug 19, 2025 · Automatically transfer data between Excel worksheets using cell references, named ranges, VBA, or Power Query for seamless syncing. Number format for a cell changes how the value is displayed, but doesn't change the underlying value. I need the hyperlink to be based on the display (formatted) value rather than the data value of the source cel In this video, you'll learn how to format cells in Excel based on the value of another cell. Apr 21, 2023 · The formatting of all cells is the same and I have also tried, in the first worksheet, copying the rows which contain the cells into which the text does copy into and inserting them under the rows with the 'faulty' cells but they then inherit the issue of the row above. With this knowledge, you can create dynamic spreadsheets that automatically update when the referenced data changes. in B1: =A1) I do get the characters, but I lose any formatting of the original cell. The table below summarizes how a reference type updates if a formula containing the reference is copied two cells down and two cells to the right. In this example, starting in B1, the cell would be A1. 4 days ago · this guide will show you how to pull data from another sheet in Excel using cell references, menus, or LOOKUP formulas with screenshots and simple instructions. Nov 4, 2023 · Learn about simple conditional formatting formulas that let you highlight data based on any values in Excel. Basic INDIRECT Example This example demonstrates the simplest use of INDIRECT to reference a cell specified by text. g. Aug 9, 2024 · The article shows 2 methods on how to keep formatting in excel when referencing cells. Jul 3, 2024 · HYPERLINK and INDIRECT functions, as well as multiple Excel features, can be used to Excel link cell to another sheet. In the first column, in the second row (A2), assuming the first row is for headers, write the sheet name. For example, you can highlight the names of sales reps in column A based on whether their sales are more than 450,000 or not (which is a value we have in cell D2). What do all those characters mean in the formula? Why doesn't mine work? Learn how to create these references easily and fully understand the anatomy of a cross reference in this article. Download practice book with formulas for free! Jul 6, 2023 · This tutorial explains how to reference text in another cell in Excel, including an example. AMAZING Excel technique. Learn smart methods to save time, reduce errors, and keep your data accurate with dynamic links. When I reference the cell through a link (e. This page lists the built-in Excel Lookup and Reference Functions. Objective Here’s an example that will allow us to put this feature into Apr 30, 2024 · In this article, I’ve explained the 6 easiest ways of Excel to highlight the cell if value is greater than another cell in Excel. Also, includes examples and shortcuts. What Is Conditional Formatting Based On Another Cell Value? The Conditional formatting based on another cell value is a powerful tool that allows users to highlight specific cells or ranges based on the contents of other cells in the same spreadsheet. May 9, 2024 · 4 Simple Ways to find Excel formula to copy cell value from another sheet or workbook. Also, how different lists can be created by Excel Data Validation. Want to highlight rows based on a cell value? In this tutorial, I will show you how to use Conditional Formatting to highlight rows in different scenarios. Feb 20, 2025 · Discover how to automatically fill Excel cells from another sheet. This post explores the details of formatting a cell or range based on the value in another cell. Dec 12, 2013 · When you want to format a cell based on the value of a different cell, for example to format a report row based on a single column’s value, you can use the conditional formatting feature to create a formatting formula. May 8, 2024 · This article shows the 13 examples of Excel format cell based on formula. During the process, you must use a suitable formula to determine which cells to format. What is the INDIRECT Function? The INDIRECT function allows you to give a text string and have the computer interpret that string as an actual reference. You use relative column & relative row references when you want to check all cells of the selected range against a certain value. This feature is essential to create dynamic reports, dashboards, and data comparisons across multiple files in business environments. Mar 21, 2023 · The tutorial explains many possible ways to copy formula in Excel: down a column, to all of the selected cells, how to copy formula exactly without changing references, and more. Oct 22, 2024 · Learn how to reference both the value and format from another cell in Excel using the linked picture option or a simple VBA script for automatic updates. in most cases, you will apply conditional formatting to the same cell for which you are analyzing the value. Jan 6, 2013 · I can make a function in a cell that picks up the content from another cell - example =A3 However; this will only show the content, and not the format from A3. , then this can be achieved by creating a rule in conditional formatting using a custom formula. From simple color changes to complex formula-based rules, mastering conditional formatting can significantly improve Nov 4, 2023 · Learn about simple conditional formatting formulas that let you highlight data based on any values in Excel. I have a spreadsheet which contains a hyperlink based on the contents of another cell. Jun 9, 2022 · When creating your conditional formatting, set the range to which it applies to what you want (the whole sheet), then enter a formula (remove the $ signs) as if you were only formatting the upper-left corner. 18 I would like to know if it is possible to reference a cell in Excel depending on the value of another cell. In Excel, you can change the cell color based on the value of another cell using conditional formatting. In the formula bar , select the reference that you want to change. Jun 24, 2024 · Built-in functions VLOOKUP, XLOOKUP, INDEX and MATCH provides the way for how to pull values from another worksheet in Excel. Aug 1, 2023 · Tired of manually formatting your Excel cells? Learn how to use conditional formatting to automatically highlight cells based on another cell's value. . You can use the TEXT function to format a value within a formula as you choose, so your second formula becomes ="I am "& TEXT(C2, "#%") &" finished" For a date Jun 12, 2020 · Explanation Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight cells greater than a specific value. You have to either use UI features like the Format Painter or Conditional Formatting of cells menu item. In this tutorial, I will cover all that you need to know about how to reference cells and ranges in Excel. Apr 23, 2024 · This article discusses 14 different effective ways to reference text in another cell with formulas, Excel tools and VBA code. R1C1 References Dec 31, 2020 · Learn to reference a cell from another cell in Excel, whether they're on the same or different worksheets, to data calculations and improve efficiency. In layman’s terms, a reference style is a style you use to refer to the cells in Excel. Press Ctrl + C to copy. Relative, absolute, and mixed are the prominent cell Conditional formatting based on external workbook values allows you to automatically format cells in one Excel workbook based on data stored in another workbook. Aug 2, 2015 · The date-time is stored in the cell as a single number in that form and then Excel derives the year, month, day, and time information from it as needed to display the value in the desired format. Mar 21, 2024 · Change number format based on a cell value. Is it possible to retain that value in the referencing cells if I e Jul 7, 2024 · The use of cell references in an Excel formula is very important when formulas are copied and pasted to other cells. Excel will properly apply the formatting to the rest of the cells accordingly. This article will explain the Jul 15, 2024 · Learn how to apply conditional formatting color scale based on another cell in Excel. , the content of A1 is 42) I want to compute the sum of column B from row 1 to the row corresponding to the number given in A1. I have an Excel spreadsheet with 2 worksheets. For this, you need to use a custom formula within the rule. Help! Aug 12, 2018 · Unfortunately, data bars need to get their data from the cell in which they exist. Sep 11, 2024 · Learn how to use conditional formatting based on another cell and create a rule that depends on another cell value in Excel. We explained them with easy examples and lucrative pictures. The second is a list of item groups, with a header row and a title in the left most CELL format codes The following list describes the text values that the CELL function returns when the Info_type argument is "format" and the reference argument is a cell that is formatted with a built-in number format. Mar 13, 2018 · The same applies to other relative references: if your conditional formatting formula has a relative reference to the cell to the right of the one you are formatting, and you copy that format somewhere else, then the format of the new cell will depend on the value to the right of the new cell. Excel functions (aka formulas) deal only with values. Learn how you can use Excel's Conditional Formatting feature based on the value of another cell. Suppose, you want to highlight all cells in columns A and B that are greater than a value in cell B1. I'm trying to apply conditional formatting in Excel on a range of cells, based on the adjacent cell's value, to achieve something like this: The goal is to highlight values in Column B (Actual Expe Oct 29, 2010 · When we copy (Ctrl + C) the formula in the image above, and paste (Ctrl + V) it into another cell, you’ll notice that the cell references change from the B column to the D column, so the total is different. Discover different techniques for referencing cells in the same or different worksheets. In this tutorial, we will cover the essential steps to reference format and value from another cell in Excel, so you can efficiently manipulate data and create dynamic spreadsheets. To be able to reference cells and ranges is what makes any spreadsheet tool work. 4 days ago · There are many reasons you'd need to use Google Sheets conditional formatting based on another cell. Aug 19, 2023 · Guide to Conditional Formatting Based on Another Cell. Learn 4 easy ways to execute this in a handsome way. And Excel is the best and most powerful one out there. Jul 6, 2024 · What Is a Cell Reference? A cell reference in Excel identifies the location of a specific cell as indicated by its column letter and row number. Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. Example I have a cell, let's say A1 where I have the row number that I want to use (e. Jul 25, 2025 · In this article, we explain how to use a cell reference in Excel and answer some frequently asked questions to help you better understand this feature. Here we discuss this along with practical examples and downloadable excel template. What is A1 Reference Style in Excel? The A1 reference style is the default reference style notation in Excel, and if you have used Excel even for a few hours, I’m assuming you have already used it. Mar 7, 2024 · When I paste a cell that has been conditionally formatted, the new pasted cell is keeping the original cell value.