How do I easily find any cells that are hard coded vs formula? I have 10,000 rows of data and column F is supposed to be a formula based on columns C and D, without clicking into all 10k cells in column F how can I make sure formulas are there and not accidentally hand keyed over?

like
Posting as :
works at
You are currently posting as works at

just do a ctrl + “~”

you’ll see the cells change to show the formulas if they have it.

likehelpfulsmart

This is super inefficient for checking 10,000 rows of data 😂

likefunny

3 ways (on top of my head)

1 - use =ISFORMULA() in the adjacent column, if there is a FALSE (or more), it means that the reference was hardcoded*

2 - go to Find and Select -> go to special -> check for constants (or formulas)*

3 - Go to Formulas in the tab bar, click "Show Formulas" and you can see on the Excel itself the formula (remember to turn it off after)

* 1 and 2 have the limitation of seeing an operation (like 2*3) as a formula instead of a constant

likehelpful

Re-write the formula and replace for entire column. We often try to find a problem but could instead just apply the solution.

likesmart

Go to conditional formatting "use formula" option. Highlight cell if =not(isformula(F2)). Then apply it to your whole range. The cells that are hard-keyed will highlight with the formatting you choose.
F2 was a just a cell example fyi.

like

Can then sort by color to bring the non formulas to the top

like

If you want the whole column to contain your formula you could just carry it down from the first cell by double clicking on the + symbol when you hover on the bottom right corner of the cell. Or you could copy the first cell in the column and ctrl + shift + down arrow to highlight every populated cell below and paste

like

If you want to find and replace hard coding, ctrl F (find), search for $, make sure you select search formulas, replace with nothing.

like

Ctl+' or search "formula auditing mode"

Related Posts

Does anyone know how to roll options in Fidelity’s app? Recently moved from TDA to fidelity

like

Hi, anyone working with DBS could refer me for a position ?

like

I want to get out of warehouse work. What's a good-paying job where I don't need a lot of schooling?

like

Hi 🐠 Best hotel in Mykonos and Santorini for honeymoon?

like
like

Best leadership books you’d recommend? promoted to manage a large team and looking for some books to get practical advices!

like

Hey guys,
I’m looking to see if there are any sysadmins here that would be willing to be my mentor?

like

What are the biggest challenge going back to the office?

likeuplifting

I work for a software coding bootcamp called Tech Elevator and am looking for Tech Recruiters to share their expertise with our students through HR mock interviews in late June. Would love to introduce you to some diverse eager Junior Tech talent before they hit the market!

Shoot me a DM-happy to chat about this opportunity or others.

like

I’ve been offered a reporting consultant job for less than 60k and I bring 9+ years of experience to the role. They should be offering more. I don’t want to settle but I don’t want to miss an opportunity to move. Reporting consultants should start at $65k per recruiting sites. Any advice? How can I get HR/recruiters to offer more? TIA

like

I work at a Pvt Ltd company in Mumbai India as a Project Manager.
I have 12+ years of experience.

What is the expected salary range for Project Managers with 12 years experience?

like

Hey Bowl,
I work for a late stage(ipo) start up, earlier this year we were acquired & since then they got rid of our yearly bonuses and gave us an ESOP grant.
2 RIFs later we are almost bare bones. Since we are coming up on our usual yearly evaluations many of us assumed (with multiple conversation from our managers) that pay raises and promotions were due.
In a recent All hands we found out that this year there will be no pay raises nor promotions based off merit. Is it time to jump ship?

like

Hi, every new joinee in JPMC gets allowance to buy laptops? Or is it specific LOBs?

like

Currently a Practice Manager at Pediatric office and non-trad pre med student majoring in computer science and I want to break into Tech now . Mainly Data Analyst . Does anyone have any suggestions on any courses or routes i should be taking besides finishing my bachelors degree?

like

Any EY-Parthenon folk willing to discuss an opening I came across? Senior Consultant in EYP strategy London

like

What are your favourite free SEO (technical and/onpage) courses or certificates?

like

What is the best site to buy Ripple? I tried using Bitstamp but those guys charge international wire transfer fees.

Hi, been stuck in the contract analysis/review cycle. I have asked a few recruiters how to make a lateral move into in-house. They told me to focus on contract management, which I have been doing for the past year. Now that I have, are there any sites besides “jobscan” that I can use to make sure my resume fits the job descriptions? Does anybody know any recruiters that could help me, as well?

like

Additional Posts in Excel Genius

How can I add multiple characteristics within one cell and make sure that a pivot table can read both values independently if needed? E.g., I have a column titled “color” and in one cell I write “red” and the other “red, blue”. I’d like a pivot table / formula to be able to count that there are technically two “reds” in this data set. Any tips?

like

Has anyone had an issue with index match and lookups returning NA when trying to manipulate data from a password protected workbook?

What would you consider "Advanced" or "Expert" level Excel usage?

like

Does anyone know any shortcuts for the “Refresh All” button without having to always click Data->Refresh All every time I want to refresh the live data? For a Mac btw pls thanks!

Post Photo
like

Can dates be grouped just by Month-Year in pivot tables on a Mac without having the year as its own dimension?

Post Photo
like

Any weekend or online excel course recommendations?

like

HELP!
I have raw sales data on one tab, which includes a sales rep ID number. My second tab has that sales rep ID number in column 1 and the rep name in column 2. How can I get the rep name added to the correct line items in the raw data?

What are your top Excel ninja tricks? Ones that few people know...

like

Everyone: Excel is a smart app
Excel:

Post Photo
likefunny

For a linear regression analysis… the equation of a line is Y=MX+B, where M is the slope. So if I’m measuring a line across 365 days and M=.2, can I say that I have increased production by 20% over a year. Or is production increasing 20% each day?

like

What is the best FP&A tool, Anaplan, Jirav, Planful, Vena? Any other recommendation?

like

Can anybody recommend any excel courses to take for financial analysis? Modeling, valuations, etc. Any direction would be helpful.@

likehelpful

Would anyone be willing to teach me vlookup?

like

What’s the keyboard shortcut to get into the “insert options” dialogue when you’ve inserted a new row with Ctrl+Shift+Plus?

like

Any suggestions for a formula to calculate a running count of the number of incidences in a list? For example, in a list with A, B, C, B; I want to label A = 1, then the first B =1, and first C = 1, and then the second B = 2, etc. The list is in a column and there are thousands of names. TIA for your help!

Best websites to get more of a handle on Excel as a beginner-intermediate?

like

Any ideas on turning string data into numerical data in excel that’s faster/easier to automate/better than using text to columns? Wish I could use alteryx, but not an option for this use case.

like

Does anyone have any tips/tricks for knowing where to plug +1/-1 in a large table with totals across the y and x axis that contain un-rounded numbers? Every time I fix a row I throw off a column or my check figures to the true data source.

Post Photo
like

I created a macro to help me save time on this onetime task and now whenever I open my excel, a blank excel opens with the name of the macro. How do I get rid of it?

like

Current IE here. What are some Time Motion Study Softwares that are out there and you use in your jobs?

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Scan your QR code to download
Fishbowl app on your mobile

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal