Passed over for promotion ? And cried in front of boss...?

Have been working for this company for 2 years. 10 years of experience. Majored in statistics. Company I work for is a Fortune 500. Applied for an analyst position. Went through rigorous 2 hr testing multiple days, teststing/interview for the job. scored right at 80% on the excel test I only had 30 minutes to do the test. I was told I didn’t get the job (which I’ve already been doing btw) because of my excel test. Of all reasons I didn’t get the job because of that.. I’m devastated.. I’ve been working for this for my whole life..I can do pivot tables, v lookups, h lookups, formulas of all sort.. everywhere I’ve worked I’ve been an expert.. The business I’m in is so complicated. I can’t imagine someone just coming in, and understanding the excel spreadsheets without some minor training, Let alone understanding our business...I told them in the interview that I’ve been working for a position like this for a long time. I said in a nice but stern way “if it’s an opportunity here, or elsewhere I don’t know but I do know I want to be in a position of this sort at some point” they cheesed and smiled. At the end I said it again and they were kind of like we heard you... I just couldn’t believe it. And that I just.. didn’t get the job.. I asked what I could do..he suggested google or classes ..( which I’ve already got..) just so insulting..After the interview, I went to my immediate supervisors office ( who’s in a different department and told him I didn’t get the job, “ I had a doctors appointment tomorrow” He was nice and told me that I really didn’t get the position because they were truly looking for someone to put ear muffs on and code all day long, sort through excel sheets. Someone with no personality. He said they were (higher management) were imagining me in a leadership/supervisor role in sales.. not analytics .. I cried a bit and I’m worried it made me look emotionally unstable. It was truly only like 2-3 tears. I said “excuse me, I never cry”..and moved on. I asked for the next day off. He reminded me that all eyes are on me after getting denied that promotion and to basically act “cool”. I said of course okay, went to desk, stayed a couple hours and noticed the person who interviewed me go into my bosses office after.. they parted ways, each left work and then I left at 5. I’m totally scared to go back to work.. can anyone share a similar story? Or any advice. I’m devastated and lost.. almost want to apply elsewhere but this truly isn’t just some ordinary job. I’ve been a total success in my current role. Like I said making pretty good money and high numbers etc.. a lot of respect. Have so much experience in stats and analytics.. took this sales job to get into that role..So.. ya just not sure where to go from here.. help..thanks for reading my story know it was long ❤️❤️❤️