I frequently use cell references like =B5 to synchronize data between different cells in my Google Sheets spreadsheet. While this method works great for copying the actual values, I’m running into an issue where the original cell’s formatting doesn’t transfer over to the referenced cell.
For instance, if my source cell has red text color, bold formatting, or specific number formatting, the destination cell just shows the plain value without any of these visual elements. This is problematic because the formatting often carries important meaning in my spreadsheets.
Is there a way to create a cell reference that copies both the content AND the formatting from the source cell? I need a solution that keeps both cells synchronized for data and appearance.
Google Sheets doesn’t provide a feature like Excel’s camera tool that copies both values and formatting together. However, there’s an effective workaround using Google Apps Script. You can create a script that monitors your source cell and automatically transfers both the value and formatting to the designated cell. By utilizing getRange().copyTo() with the appropriate paste parameters, you can ensure that everything syncs seamlessly, including conditional formatting and text styles. While it requires some initial setup, the process becomes entirely automated once configured.
This drove me absolutely nuts when I was building dashboard templates. You can’t reference formatting in formulas - only data transfers over. I tried everything before finding what actually works: INDIRECT references + smart conditional formatting rules. Point your destination cells to source cells using INDIRECT, then set up conditional formatting that matches your visual styling based on cell values. Make sure your formatting rules cover every scenario you need. Yeah, it’s more setup work upfront, but once it’s done, everything stays synced without any script maintenance BS. Plus it works across different sheets.
Google Sheets formulas only grab raw values - no formatting. I’ve dealt with this for years building financial models where color coding matters. My workaround? Use IMPORTRANGE and sync the formatting manually. Here’s what works: create a master template with all your formatting already set up in the destination cells. Then add conditional formatting rules based on your imported values to trigger the right visual changes. Not as clean as copy-paste-special, but it keeps everything looking consistent without needing scripts. Just make sure your conditional formatting rules match whatever logic you’re using in the source cells.
I’ve hit this same issue tons of times. Google Sheets formula references grab values but completely ignore formatting.
Apps Script works but becomes a nightmare when you’re dealing with multiple sheets. Trust me, I learned this managing dozens of connected spreadsheets.
What fixed it for me? I moved everything outside Google Sheets. Built an automation that watches my source sheet, grabs the data AND formatting, then pushes it all to the right cells.
Now I get real-time syncing of values and formatting across sheets without touching anything. I can even add conditions for when syncing should happen.
The automation handles formatting detection and application automatically. No more manually copying conditional formatting or wrestling with complex scripts.
You can build something like this with Latenode. It connects directly to Google Sheets and handles data plus formatting transfers between cells or different spreadsheets.
honestly, paste special is probably your best option. formulas don’t carry formatting - that’s just how sheets works. when i need both synced, i paste special > values and formatting manually whenever things change. not automated, but way simpler than dealing with broken scripts.
yeah, i have the same issue! what i did was apply the same conditional formatting rules to both cells. whenever i update the source, the formats change automatically too. it’s not perfect but it’s better than doing it all by hand!