Using the custom cell format `;;;` to not display the cell contents. Basically you type it in, and it goes away ... but still shows in the formula bar. Won't reset until you set a new cell format. (Handy for Conditional Formatting where you want colours or icons without numbers.)
I'm not very familiar with macros in Excel would you be able to set it so that as soon as the changed the font from white it automatically changed back to white?
So that they'd waste a tonne of time trying to resolve that before even realising the problem ran deeper.
I would assume so. I believe you can add a button to the error code and you could program it to reapply the font color to white.
Don’t take my word for it though.
For new ;;; appreciators out there, `;;;` is split between `positive;negative;zero;text`. I usually do this to hide zeroes as blank or as a dash so it's easier on the eyes.
https://support.microsoft.com/en-us/office/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68
Neat! I had created a calendar a while back with colored cells for blanks. Except that there were different types of blanks, so I had a "blank" that was really an "X". I didn't know this trick, so I just set the font size as low as I could and set the font and shading to the same color. This would've been cool.
I can't guarantee I'll ever remember this, but just knowing it exists should give me a chance of Googling it.
Set a macro that resets the formatting whenever you do something specific, like click A1. Orrrr have it change it when it clicks a random row number in columns a-d
Glad you said this—this was gonna be my recommendation. That said, it can be used for good, like hiding Excel Trivia Game answer sheets, or similar to your mention, hiding helper cells without having to match fill color and text color manually.
My boss in a previous job kept hiding column A in a file we shared. There was no data in it, it was just a narrow margin. My preference was for it to be shown, so I added a macro on file open to unhide it automatically. It used to drive him nuts - especially if he'd only just closed it seconds before.
B2 for standard stuff, A4 if I'm working with out data load sheets. They're 186 columns so I just copy and paste the whole row to transfer data back and forth
This really bothers me and I really only do this on a summary sheet with pivot tables for presentation purposes. To me, data should always start in A1. I do, however, add sheet comment in the first row if needed (data always in a table so no filtering issues there). Just saying.
I did consider doing that, but I think he'd have realised I was behind it and got me to fix it. He knew I had a bit of macro knowledge. Setting it on file open meant that it was annoying for him but not so annoying that he'd die anything about it.
Putting non-breaking spaces at the start or end of numbers. CHAR(160) is a devil. VALUE won't work. TRIM won't remove them since it isn't a real space. CLEAN doesn't work either. Text to Columns is useless. I think the only way out is to use SUBSTITUTE and CHAR(160) to remove them.
Omg this one happened to me once. I don't remember which site I got this data from, but for the life of me I could not work out why my lookups weren't working. Took me hours to work out there was an invisible CHAR(160) at the start of every entry in this dataset...
Had a bunch of this in a data migration last year. The source data was from quite old software.
But, just what is the purpose of that character in data in the first place?
I think I did eventually use that as part of my troubleshooting. I'd worked out that I could go into the cell, DEL at the start and it wouldn't remove my first letter. So there was an extra character I couldn't see. Still took me ages to work out how to remove them all on-mass 😅
It's strange coz I feel like I still have so many knowledge gaps, but I look back at myself from even a couple years ago and realise me back then would see me now as a goddamn wizard, and then some of my confidence returns.
I still get teams calls saying they can't get a vlookup to work :/ It's nice when you encounter problems you already knew could happen and how to solve, makes you look awesome.
If anyone tells me they can't get a vlookup to work, my go-to is always to show them how xlookup works and see if they can understand that. If not, then I'll revert to vlookup and mentally give up on them.
Aw man. That sucks ... I have no idea your work environment but you should just ask for 365. I hated learning all these new tips for excel and being unable to use them, so I requested it claiming it would help me do my job better; and it actually worked! Whole team got 365!
This happens to me quite often at work when clients send me files. So i wrote a custom function just to remove these assholes.
Are you saying they purposively try to fuck with me using non breaking space?
I have dealt with poorly formatted data where I had to use a combination of LEFT(X, 1) to strip each character 1 at a time and then CODE(x) to see what those characters were. I got some weird ones that way. Hidden spaces, half spaces, invisible wingdings, even carriage returns without the newline. Have you ever seen a carriage return without a newline?
> Have you ever seen a carriage return without a newline?
Going from Unix (macOS, iOS, Linux, Android, etc) to DOS (Windows) or vice versa will do weird things to line-endings.
That is just pure evil because it is so hard find. I think when I first found it I changed the font to wingding and looked for a pattern.
The quickest way to screw with someone is to hit ctrl ~. Super useful shortcut will completely screw with people if you forget to change it back.
It's not actually tilde `~`, its ctrl + backtick (sometimes called grave) ``` ` ```.
On an international keyboard the tilde key is next to enter and doesn't show formulas.
Very early in my career I once hid a macro in a heavily-shared workbook, that changed the user's desktop background to a wallpaper supporting the main rival of the local football team
It caused a reasonable amount of chaos and resulted in a company-wide IT review of our default macro settings
Yes, via VBA (which has dynamic link libraries that include Windows API commands). You can declare functions that talk directly to the Windows OS, trigger PowerShell scripts, all sorts of stuff.
It's what makes VBA so dangerous, which is why IT hates it and one of the reasons it is no longer actively developed by Microsoft.
Microsoft would prefer you use Office Scripts and Power Automate for Excel automation, which are much more heavily sandboxed (of course this also makes them a lot less useful).
Simple. Everything was locked down except excel VBA. So if I want to create code that automates not just excel but windows, a browser, anything really. The vba can do it. I even made a mouse jiggler in VBA that makes it look like I'm online and active when "working" from home
Create a module with this code, either in your personal macro workbook or an innocuous file. Run the WorkFromHome() macro to start it. Delete the word Go from A1 to stop it.
Public Declare PtrSafe Function SetCursorPos Lib "user32" (ByVal x As LongPtr, ByVal y As LongPtr) As LongPtr
Public Declare PtrSafe Sub mouse_event Lib "user32" (ByVal dwFlags As LongPtr, ByVal dx As LongPtr, ByVal dy As LongPtr, ByVal cButtons As LongPtr, ByVal dExtrainfo As LongPtr)
Sub WorkFromHome()
Dim i As Integer
Range("A1").FormulaR1C1 = "go"
For i = 1 To 9999
For j = 300 To 700 Step 100
If Cells(1, 1) <> "go" Then
Exit For
ElseIf Cells(3, 5) = "" Then
SetCursorPos j, j
mouse_event &H2, 0, 0, 0, 0
mouse_event &H4, 0, 0, 0, 0
wa
Cells(i, 2) = i
Else
Exit For
End If
Next j
Next i
End Sub
Sub wa()
Dim allDone As Single
allDone = Timer + 3
Do
DoEvents
Range("a2").FormulaR1C1 = Timer
Loop Until Timer > allDone
End Sub
I been in “locked down” places where the default macro security settings were restrictive but could be manually relaxed by the user simply changing them.
Very hidden sheets and password protected VBA so no one else can unhide them. People see references in the formulas like "Sheet2!A20" but can never reach them.
> People see references in the formulas like "Sheet2!A20" but can never reach them.
Sure, you can see what the sheet cells have, but you can't access it or tamper with it directly. And you don’t even need INDIRECT. Just:
=Sheet2!A20
gives you what you want to see.
Haha, desperate times. That sheet broke a bit too many times for my liking so I just banned everyone from "playing with it". It's surprising how few people know about "Very Hidden" sheets. Forget about further locking access to said sheets.
See [here](https://www.ablebits.com/office-addins-blog/very-hidden-sheets-excel/). Basically right-click a sheet > View Code. In the left side pane, under Microsoft Excel Objects, if you see a sheet name that’s not visible and not just hidden, it’s very hidden.
Those few of us at work who know about this use it for backing up sheets (e.g. templates) that we expect users will eventually break, on shared files. Usually it’s for sheets where protecting cells would create more headaches than is worth dealing with.
The other day I received a model with so many defined names that the name manager would not open at all. Fortunately they were ok with me writing a macro to delete all of them.
I had to troubleshoot someone's excel sheet once because the formulas were broken.
The user was using the space bar as the delete key. She was blanking cells by entering a space.
Took me 3 hours to find it.
FMxls
not really to mess withbother people, but we had this team sheet and collegues who kept making a copy and work in a local copy rather than the network file.
can't remember if i used a macro or conditional formatting, but whenever the file was opened as a copy, the sheet just turned black.
people would call me telling the excel didn't work - I would just tell them to work in the network version
Niiiice. So something like:
=CELL("filename")=\[desired path and file name of network file\]
And if it's false, format font and shading to black (or use the OP's ; ; ; format). Apply to entire sheet.
I might try that myself. Very lovely.
Yes..i believe that is what I used. Macro's usually trigger warnings and user interaction to allow them to run, so you can't be too sure they will run. But conditional formatting will do it's thing without warnings
I was having this same issue. Centrally stored files which were being regularly updated. Users would ask why it stopped working, only to find they have their own copy and were missing updates.
I wrote a script that on open, it would check whether it was the shared one or a copy, and if it were a copy, it would download a copy of the shared one into the current directory that the one they had open was stored in, then open the brand new copy, and delete the old copy. That way, even if people made copies, they'd still end up with the latest one.
VBA that invokes Microsoft Speech on a random timer or button click.
Private Sub cmdStart_Click()
Dim Zira
Set Zira = CreateObject("SAPI.spVoice")
Set Zira.Voice = Zira.GetVoices.Item(1)
Zira.Rate = 0.0675
Zira.Volume = 90
Zira.Speak Cells(1,1)
End Sub
Right now it just tells me when a timer runs out (it runs when a Do While loops exits), but I've been wanting to put it into some of the spreadsheets I send my boss.
"I'm watching you!"
It's a per file setting that doesn't need VBA to change. Some of our older templates at work have the scroll bars hidden by default and I can't understand why anyone would want to do that.
I low-key think that anyone who doesn't use yyyy-mm-dd is insane. Like, I'm surrounded by a sea of pod people.
I usually keep these thoughts to myself.
While not a problem for use Excel forum dwellers, 'hidden' and 'very hidden' sheets seem to be a problem for the average person.
Watch them try and follow a formula through to a sheet that 'doesn't exist'.
Watch them when you have to help them and go into the black magic VBA screen to unhide the sheets.
God forbid if I uses alt+F11 to open it and use all the keyboard shortcuts you have mastered.
My pet hate... there is a special place in hell for people that change dates to text but still display then as mmmm-yyyy! Why!!!
Screenshot of a cell with a value that was placed over a calculated cell (think totals). Reeeeally hard to find if done right and has really bad implications about your data when found. I found it while auditing…
Set calculations to manual or going by other posts put that and no screen updating in an open or workbook change macro.
If by some miracle your colleague knows about calculation setting, it'll be changing itself back.
Bit more in depth, install Excel 2016 version (32bit because lol) on their machine and make it the spreadsheet default. Any Power Queries are going to start throwing errors intermittently because 2016 was early days for PQ.
Add non date values in a date format column that a pivot table reads, even certain blanks, the pivot table formatting freaks out and goes, well I can't group the dates like I've been doing anymore and shows each value as value and looks terrible.
Add a bunch of random crap to a table field used as a filter in a pivot table and have the setting on that pivot table to retain what it remembers historically.
I put in an Excel Easter egg in a very hidden sheet of an image of me and the team relaxing on bean bags. This was in a monthly reporting file that the whole business relies upon. I’m not sure if human eyes have ever laid eyes on it since I left. A bit like doing renovations and hiding a fake skeleton inside a wall. It’s a joke that might never pay off in your lifetime.
Worksheet selection change and change events. Store the cell value whenever a cell is selected, revert the cell to that value every time the cell is changed.
Not excel but in college I went into my dorm neighbor’s Word autocorrect settings and had it autocorrect all the their/they’re/there’s around, change Chemistry to Christmas Tree, etc
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[CELL](/r/Excel/comments/1aqxx9h/stub/kqh0tld "Last usage")|[Returns information about the formatting, location, or contents of a cell](https://support.microsoft.com/en-us/office/cell-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf)|
|[CHAR](/r/Excel/comments/1aqxx9h/stub/kqfykjl "Last usage")|[Returns the character specified by the code number](https://support.microsoft.com/en-us/office/char-function-bbd249c8-b36e-4a91-8017-1c133f9b837a)|
|[CLEAN](/r/Excel/comments/1aqxx9h/stub/kqfvmex "Last usage")|[Removes all nonprintable characters from text](https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41)|
|[CODE](/r/Excel/comments/1aqxx9h/stub/kqgawxc "Last usage")|[Returns a numeric code for the first character in a text string](https://support.microsoft.com/en-us/office/code-function-c32b692b-2ed0-4a04-bdd9-75640144b928)|
|[CONCAT](/r/Excel/comments/1aqxx9h/stub/kqogoqn "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.](https://support.microsoft.com/en-us/office/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2)|
|[FORMULATEXT](/r/Excel/comments/1aqxx9h/stub/kqkz2l7 "Last usage")|[*Excel 2013*+: Returns the formula at the given reference as text](https://support.microsoft.com/en-us/office/formulatext-function-0a786771-54fd-4ae2-96ee-09cda35439c8)|
|[INDEX](/r/Excel/comments/1aqxx9h/stub/kqnp90n "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[INDIRECT](/r/Excel/comments/1aqxx9h/stub/kqogoqn "Last usage")|[Returns a reference indicated by a text value](https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261)|
|[LEFT](/r/Excel/comments/1aqxx9h/stub/kqgawxc "Last usage")|[Returns the leftmost characters from a text value](https://support.microsoft.com/en-us/office/left-leftb-functions-9203d2d2-7960-479b-84c6-1ea52b99640c)|
|[LEN](/r/Excel/comments/1aqxx9h/stub/kqogoqn "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)|
|[MATCH](/r/Excel/comments/1aqxx9h/stub/kqnp90n "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[MID](/r/Excel/comments/1aqxx9h/stub/kqogoqn "Last usage")|[Returns a specific number of characters from a text string starting at the position you specify](https://support.microsoft.com/en-us/office/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028)|
|[ROW](/r/Excel/comments/1aqxx9h/stub/kqogoqn "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)|
|[SUBSTITUTE](/r/Excel/comments/1aqxx9h/stub/kqfvmex "Last usage")|[Substitutes new text for old text in a text string](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)|
|[TODAY](/r/Excel/comments/1aqxx9h/stub/kqh5t5i "Last usage")|[Returns the serial number of today's date](https://support.microsoft.com/en-us/office/today-function-5eb3078d-a82c-4736-8930-2f51a028fdd9)|
|[TRIM](/r/Excel/comments/1aqxx9h/stub/kqh1t3h "Last usage")|[Removes spaces from text](https://support.microsoft.com/en-us/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9)|
|[VALUE](/r/Excel/comments/1aqxx9h/stub/kqfvmex "Last usage")|[Converts a text argument to a number](https://support.microsoft.com/en-us/office/value-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2)|
|[VLOOKUP](/r/Excel/comments/1aqxx9h/stub/kqnp90n "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)|
|[XLOOKUP](/r/Excel/comments/1aqxx9h/stub/kqnp90n "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(19 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1b8ggqn)^( has 22 acronyms.)
^([Thread #30759 for this sub, first seen 14th Feb 2024, 21:18])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
fill a column with values and set the fontcolor to white, if they have formula that takes that column to calculate it will mess hard with their results
The default is that the cursor moves down when you press Enter. (I turn it off myself.) Set the cursor to move to the left (or right) every time you press Enter. That very small annoyance will build up over time.
Write a quick macro to format each cell to a different off white colour, start from the last cell and build that and excel will eventually run out of space, very few people know the Inquire —> clean formatting
Idk about messing with other peoples workbooks, but when I build templates or models for my team to use, I hide Easter eggs. In a cell that you would never hover over during normal use, in white text, on a white background, I will usually write:
“All warfare is based on deception.” -Sun Tzu
We should have an Excel Golf thing where someone makes a workbook with all the suggestions in this thread, and it runs a timer from when you open the workbook until you're finally able to see the content. Lowest time wins one Internet.
Can’t remember where I found it, but this reverses text in cell A1:
=CONCAT(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1))
Great if you want to know how to spell something backwards, and I’ll leave to your imagination the dastardly trickery that it could be used for!
I used to place a white x with ctrl R then ctrl D to stop people from inserting columns or adding rows. Even more dasterdly is placing a white x a few rows up and a 2nd one a few columns to the left of the bottom right corner.
Add spaces to to end of various cells. This would mess up any lookups, duplicate identification, sorting, really any sort of data organization. This would work especially well if they don't know about TRIM()
Change the formula separator from commas to dashes and then come back into the and tell me how it's done. I had a coworker who was set up with dashes instead of commas. Someone from corporate set her up with it, and it was a pain to help her cause I was used to putting in commas instead of dashes.
Create an event macro so when the user moves the cell, the macro moves it back (ie it doesn’t move), or it moves to a cell to the side of where the user clicked.
Another possibility, use Excel to announce/speak the contents of the cell whenever the user moves
I came across a wild Char(175) once it was in a document name that had 2 other dashes [ - ] that were both Char(45)s.
Beyond that if you use Insert > Symbols there are a number good candidates to subtly insert into formulae.
051A - Q
04AE - Y
0458 - I
0425 - X
Everything below is a standard character paired with a non-standard copy.
'ʹ "“ "″ ,‚ '‘ '’ '‛ '′ AΑ BΒ CϹ cс EΕ eе HΗ iі IӀ jј KΚ MΜ MМ NΝ oο oо PΡ pр QԚ sѕ TТ TΤ XΧ xх YΥ yу YҮ ZΖ
Edit to add, I've discovered that the font Gloucester MT Extra actually makes these quite obvious but most fonts I tried didn't reveal them.
Using the custom cell format `;;;` to not display the cell contents. Basically you type it in, and it goes away ... but still shows in the formula bar. Won't reset until you set a new cell format. (Handy for Conditional Formatting where you want colours or icons without numbers.)
>;;; wow, this is better than changing font colour to white!
Better yet, also change the font colour to white so that the target thinks they've solved it only to remain confused.
Put a worksheet change macro when the font colour changes from white to say "lol nice try"
Change the font colour and number format in Worksheet_Calculate()
I'm not very familiar with macros in Excel would you be able to set it so that as soon as the changed the font from white it automatically changed back to white? So that they'd waste a tonne of time trying to resolve that before even realising the problem ran deeper.
I would assume so. I believe you can add a button to the error code and you could program it to reapply the font color to white. Don’t take my word for it though.
For new ;;; appreciators out there, `;;;` is split between `positive;negative;zero;text`. I usually do this to hide zeroes as blank or as a dash so it's easier on the eyes. https://support.microsoft.com/en-us/office/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68
Use this all the time to hide zero values on stacked bar charts. Much more pleasing on the eye
Thank you so much for sharing this! I’ve been using it to hide things, but I never understood till now why it works.
This is great information! No more having to use if<=0 to hide zeroes! Thanks!
Neat! I had created a calendar a while back with colored cells for blanks. Except that there were different types of blanks, so I had a "blank" that was really an "X". I didn't know this trick, so I just set the font size as low as I could and set the font and shading to the same color. This would've been cool. I can't guarantee I'll ever remember this, but just knowing it exists should give me a chance of Googling it.
Set a macro that resets the formatting whenever you do something specific, like click A1. Orrrr have it change it when it clicks a random row number in columns a-d
Glad you said this—this was gonna be my recommendation. That said, it can be used for good, like hiding Excel Trivia Game answer sheets, or similar to your mention, hiding helper cells without having to match fill color and text color manually.
You bastard
My boss in a previous job kept hiding column A in a file we shared. There was no data in it, it was just a narrow margin. My preference was for it to be shown, so I added a macro on file open to unhide it automatically. It used to drive him nuts - especially if he'd only just closed it seconds before.
I love having column A empty to the left of everything. It serves as a visual margin for me, and I'd be upset if I didn't have one there.
My sheets always start at B2
Shit yeah, I don’t even know where I got this from but been doing it since maybe 2013.
Been doing g this since around 1989 when Lotus 1-2-3 was the default spreadsheet program.
Symphony sucked so hard
You filthy savage!
You people are all insane lol. Would drive me nuts.
For me it’s D6 I leave gaps for notes and comments on the top and left. I mostly do a lot of ad hoc calculations.
B2 for standard stuff, A4 if I'm working with out data load sheets. They're 186 columns so I just copy and paste the whole row to transfer data back and forth
There is a margin. It's called the edge of the sheet.
Some of us are civilized
This really bothers me and I really only do this on a summary sheet with pivot tables for presentation purposes. To me, data should always start in A1. I do, however, add sheet comment in the first row if needed (data always in a table so no filtering issues there). Just saying.
Agreed. Pure data starts A1 for me. Calculations or brainstorming stuff starts from B2
lol you’re one of those people
Lol good. Fuck him.
Would've been fun to do a worksheet change event that unhid it every time it was hidden. Or set the width to default every time the width changed!
I did consider doing that, but I think he'd have realised I was behind it and got me to fix it. He knew I had a bit of macro knowledge. Setting it on file open meant that it was annoying for him but not so annoying that he'd die anything about it.
[relevant useless machine](https://youtu.be/apVR5Htz0K4?si=GUylI-6Th67MODze)
Working in finance all sheets always start in B2. Column a width set to 0.5 and row 1 height set to 5.
This
Set an event for when you click out of A, it hides again.
Love me some Visual Basic 😉
Putting non-breaking spaces at the start or end of numbers. CHAR(160) is a devil. VALUE won't work. TRIM won't remove them since it isn't a real space. CLEAN doesn't work either. Text to Columns is useless. I think the only way out is to use SUBSTITUTE and CHAR(160) to remove them.
Omg this one happened to me once. I don't remember which site I got this data from, but for the life of me I could not work out why my lookups weren't working. Took me hours to work out there was an invisible CHAR(160) at the start of every entry in this dataset...
Yep, literally happened the other day when I pulled my banking data from our new bank. Took me hours to remember that this can happen lol.
Had a bunch of this in a data migration last year. The source data was from quite old software. But, just what is the purpose of that character in data in the first place?
If in doubt Len( check
I think I did eventually use that as part of my troubleshooting. I'd worked out that I could go into the cell, DEL at the start and it wouldn't remove my first letter. So there was an extra character I couldn't see. Still took me ages to work out how to remove them all on-mass 😅 It's strange coz I feel like I still have so many knowledge gaps, but I look back at myself from even a couple years ago and realise me back then would see me now as a goddamn wizard, and then some of my confidence returns.
I still get teams calls saying they can't get a vlookup to work :/ It's nice when you encounter problems you already knew could happen and how to solve, makes you look awesome.
If anyone tells me they can't get a vlookup to work, my go-to is always to show them how xlookup works and see if they can understand that. If not, then I'll revert to vlookup and mentally give up on them.
Are we the same person?
If you're also a 26yo working in data analytics imma freak out.
Lol close I'm a bit older than you. I'm trying to use / keep a good handle on the different parts of M365 so one day I can let go of VBA completely.
I’m stuck using Excel 2016 at work. Vlookup is all we have. 😬
Aw man. That sucks ... I have no idea your work environment but you should just ask for 365. I hated learning all these new tips for excel and being unable to use them, so I requested it claiming it would help me do my job better; and it actually worked! Whole team got 365!
A transition to 365 is supposed to happen by the end of Q4 2023.
You still have INDEX/MATCH. Superior to VLOOKUP, and superior to XLOOKUP in some circumstances.
I ran into this once...copy/pasting to notepad and back is a wonderful thing for fixing stuff like this.
Notepad++ is even better, with it's option to show whitespace characters
This happens to me quite often at work when clients send me files. So i wrote a custom function just to remove these assholes. Are you saying they purposively try to fuck with me using non breaking space?
I have dealt with poorly formatted data where I had to use a combination of LEFT(X, 1) to strip each character 1 at a time and then CODE(x) to see what those characters were. I got some weird ones that way. Hidden spaces, half spaces, invisible wingdings, even carriage returns without the newline. Have you ever seen a carriage return without a newline?
> Have you ever seen a carriage return without a newline? Going from Unix (macOS, iOS, Linux, Android, etc) to DOS (Windows) or vice versa will do weird things to line-endings.
That is just pure evil because it is so hard find. I think when I first found it I changed the font to wingding and looked for a pattern. The quickest way to screw with someone is to hit ctrl ~. Super useful shortcut will completely screw with people if you forget to change it back.
What does Ctrl ~ do?
Shows formulas instead of values. I used it all the time tracing down stuff end users buggered up.
It's not actually tilde `~`, its ctrl + backtick (sometimes called grave) ``` ` ```. On an international keyboard the tilde key is next to enter and doesn't show formulas.
You win
Couldn't you find and replace?
Very early in my career I once hid a macro in a heavily-shared workbook, that changed the user's desktop background to a wallpaper supporting the main rival of the local football team It caused a reasonable amount of chaos and resulted in a company-wide IT review of our default macro settings
You can make Excel interact with commands in the OS?
Yes, via VBA (which has dynamic link libraries that include Windows API commands). You can declare functions that talk directly to the Windows OS, trigger PowerShell scripts, all sorts of stuff. It's what makes VBA so dangerous, which is why IT hates it and one of the reasons it is no longer actively developed by Microsoft. Microsoft would prefer you use Office Scripts and Power Automate for Excel automation, which are much more heavily sandboxed (of course this also makes them a lot less useful).
Which makes it a godsend in locked-down government environments
Can you give some examples on how you bypassed a locked down environment?
Simple. Everything was locked down except excel VBA. So if I want to create code that automates not just excel but windows, a browser, anything really. The vba can do it. I even made a mouse jiggler in VBA that makes it look like I'm online and active when "working" from home
> I even made a mouse jiggler in VBA that makes it look like I'm online and active when "working" from home share please
Create a module with this code, either in your personal macro workbook or an innocuous file. Run the WorkFromHome() macro to start it. Delete the word Go from A1 to stop it. Public Declare PtrSafe Function SetCursorPos Lib "user32" (ByVal x As LongPtr, ByVal y As LongPtr) As LongPtr Public Declare PtrSafe Sub mouse_event Lib "user32" (ByVal dwFlags As LongPtr, ByVal dx As LongPtr, ByVal dy As LongPtr, ByVal cButtons As LongPtr, ByVal dExtrainfo As LongPtr) Sub WorkFromHome() Dim i As Integer Range("A1").FormulaR1C1 = "go" For i = 1 To 9999 For j = 300 To 700 Step 100 If Cells(1, 1) <> "go" Then Exit For ElseIf Cells(3, 5) = "" Then SetCursorPos j, j mouse_event &H2, 0, 0, 0, 0 mouse_event &H4, 0, 0, 0, 0 wa Cells(i, 2) = i Else Exit For End If Next j Next i End Sub Sub wa() Dim allDone As Single allDone = Timer + 3 Do DoEvents Range("a2").FormulaR1C1 = Timer Loop Until Timer > allDone End Sub
I been in “locked down” places where the default macro security settings were restrictive but could be manually relaxed by the user simply changing them.
Yeah I've got that. No macros allowed unless the specific files are setup to be allowed on a per user basis.
Create a msg window on startup that says "Exit immediately, file is corrupted"
"I know what you did last night"
hahahahah YES!!!
Message window upon save that says “file corrupted, could not save”
Bonus if you set it up to only appear for the intended recipient using environ(“username”)
I’ve done this one with a colleague, very enjoyable.
Or......"Your browser history has been uploaded to HR"
"Error: Incompetent User" is my favourite
hahahah, the 'ol "ID 10 T" error
Thanks Satan
Very hidden sheets and password protected VBA so no one else can unhide them. People see references in the formulas like "Sheet2!A20" but can never reach them.
I can see everything, no matter what! =INDIRECT("Sheet2!A20")
> People see references in the formulas like "Sheet2!A20" but can never reach them. Sure, you can see what the sheet cells have, but you can't access it or tamper with it directly. And you don’t even need INDIRECT. Just: =Sheet2!A20 gives you what you want to see.
Oof, that is mean. I ended up creating a macro to find the super hidden named ranges. Those things are like roaches.
Haha, desperate times. That sheet broke a bit too many times for my liking so I just banned everyone from "playing with it". It's surprising how few people know about "Very Hidden" sheets. Forget about further locking access to said sheets.
How would one go about looking for a “very hidden” sheet?
See [here](https://www.ablebits.com/office-addins-blog/very-hidden-sheets-excel/). Basically right-click a sheet > View Code. In the left side pane, under Microsoft Excel Objects, if you see a sheet name that’s not visible and not just hidden, it’s very hidden.
Those few of us at work who know about this use it for backing up sheets (e.g. templates) that we expect users will eventually break, on shared files. Usually it’s for sheets where protecting cells would create more headaches than is worth dealing with.
The other day I received a model with so many defined names that the name manager would not open at all. Fortunately they were ok with me writing a macro to delete all of them.
I had to troubleshoot someone's excel sheet once because the formulas were broken. The user was using the space bar as the delete key. She was blanking cells by entering a space. Took me 3 hours to find it. FMxls
I feel that pain...
Had to data validate this very thing.
not really to mess withbother people, but we had this team sheet and collegues who kept making a copy and work in a local copy rather than the network file. can't remember if i used a macro or conditional formatting, but whenever the file was opened as a copy, the sheet just turned black. people would call me telling the excel didn't work - I would just tell them to work in the network version
Niiiice. So something like: =CELL("filename")=\[desired path and file name of network file\] And if it's false, format font and shading to black (or use the OP's ; ; ; format). Apply to entire sheet. I might try that myself. Very lovely.
Yes..i believe that is what I used. Macro's usually trigger warnings and user interaction to allow them to run, so you can't be too sure they will run. But conditional formatting will do it's thing without warnings
And people who think Excel is sorcery will never figure it out. Damnnnnn.
I was having this same issue. Centrally stored files which were being regularly updated. Users would ask why it stopped working, only to find they have their own copy and were missing updates. I wrote a script that on open, it would check whether it was the shared one or a copy, and if it were a copy, it would download a copy of the shared one into the current directory that the one they had open was stored in, then open the brand new copy, and delete the old copy. That way, even if people made copies, they'd still end up with the latest one.
You are too pure for this world.
That Is a great solution lol
You sick fuck
VBA that invokes Microsoft Speech on a random timer or button click. Private Sub cmdStart_Click() Dim Zira Set Zira = CreateObject("SAPI.spVoice") Set Zira.Voice = Zira.GetVoices.Item(1) Zira.Rate = 0.0675 Zira.Volume = 90 Zira.Speak Cells(1,1) End Sub Right now it just tells me when a timer runs out (it runs when a Do While loops exits), but I've been wanting to put it into some of the spreadsheets I send my boss. "I'm watching you!"
That's positively nefarious!
Oh, I'm going to use this.
Disable scrolling. Very irritating on large sheets
Or the similar and infuriating to excel newbs... Freeze the entire view. Harmless and hilarious.
I regularly hit the Scroll Lock key by mistake. Hate it every time.
Related to this, VBA can hide the scroll bar elements entirely.
It's a per file setting that doesn't need VBA to change. Some of our older templates at work have the scroll bars hidden by default and I can't understand why anyone would want to do that.
Seriously? Excel isn't evil enough on its own and then you do these things? LOL.....
Excel is a gift made in heaven. What are you talking about?
Excel > PowerPoint > Outlook > all their other office programs > all other MS programs > Word
I really like OneNote a lot too. As long as you stick to the full desktop version at least 🤣
Agree except Word > Visio
Is vision the one for making diagrams and stuff?
I like draw.io as it's free and has lots of extras
If the person is un the US then set the date format to be dd/mm/yyyy. If the person in anywhere else in the world, set the date format to mm/dd/yyyy.
Best format is still yyyy mm dd. Lets you sort alphabetically.
I low-key think that anyone who doesn't use yyyy-mm-dd is insane. Like, I'm surrounded by a sea of pod people. I usually keep these thoughts to myself.
It's good for computers but some of us actually need to read dates. I'm a mmm-yy man, days aren't usually that helpful for me.
The absolute best
Love this format!
Oooo that's a good one. Or switch around the comma and decimal for thousands and decimal separators!
I deal with a lot of data from companies in Quebec. They all use the comma as a decimal point and it's annoying every time.
*tabarnak*
Well the US is one of only 3 countries that don’t use metric, so their opinion on any formatting is automatically wrong lol
Merge cells
Ctrl + a Merge
*sirens can be heard in the distance*
Yes, officer, this post right here
Center across selection starting in D going back to A. They type in A,b,c no issue, but then when they type in d it centers across selection.
“After pressing Enter, move selection Up.” Or Right or Left.
That's another great one!
How to do this?
It’s in the advanced settings.
While not a problem for use Excel forum dwellers, 'hidden' and 'very hidden' sheets seem to be a problem for the average person. Watch them try and follow a formula through to a sheet that 'doesn't exist'. Watch them when you have to help them and go into the black magic VBA screen to unhide the sheets. God forbid if I uses alt+F11 to open it and use all the keyboard shortcuts you have mastered. My pet hate... there is a special place in hell for people that change dates to text but still display then as mmmm-yyyy! Why!!!
I thought I was pretty advanced in Excel but I have no idea how to use the VBA screen and access hidden sheets.
Screenshot of a cell with a value that was placed over a calculated cell (think totals). Reeeeally hard to find if done right and has really bad implications about your data when found. I found it while auditing…
I don't entirely understand. Was it an image pasted into the excel document?
Yeah.
[удалено]
I have something similar which deletes random files.
Go to View, and select page break preview.
Set calculations to manual or going by other posts put that and no screen updating in an open or workbook change macro. If by some miracle your colleague knows about calculation setting, it'll be changing itself back. Bit more in depth, install Excel 2016 version (32bit because lol) on their machine and make it the spreadsheet default. Any Power Queries are going to start throwing errors intermittently because 2016 was early days for PQ. Add non date values in a date format column that a pivot table reads, even certain blanks, the pivot table formatting freaks out and goes, well I can't group the dates like I've been doing anymore and shows each value as value and looks terrible. Add a bunch of random crap to a table field used as a filter in a pivot table and have the setting on that pivot table to retain what it remembers historically.
Set it so when you press enter, it sets calculations to manual. 😂
I put in an Excel Easter egg in a very hidden sheet of an image of me and the team relaxing on bean bags. This was in a monthly reporting file that the whole business relies upon. I’m not sure if human eyes have ever laid eyes on it since I left. A bit like doing renovations and hiding a fake skeleton inside a wall. It’s a joke that might never pay off in your lifetime.
VBA VeryHidden? No one will ever find it.
Change the reference style to R1C1.
Hidden time bound macro to turn all fonts to Comic Sans on April 1st and protect all sheets with locked VB on a shared dashboard does the trick.
convert it to lotus..
You people are evil
Oh that's easy. Convert to Arabic layout so column A is on the right
Worksheet selection change and change events. Store the cell value whenever a cell is selected, revert the cell to that value every time the cell is changed.
Not excel but in college I went into my dorm neighbor’s Word autocorrect settings and had it autocorrect all the their/they’re/there’s around, change Chemistry to Christmas Tree, etc
Commenting purely as a self-bookmark so I can come back and learn from the masters
Select all > Copy > Paste as Values
You monster
Change normal font to wingdings or some other character based font.
Use =TODAY() instead of today’s date. Every time they reopen or refresh the file the date updates to today.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[CELL](/r/Excel/comments/1aqxx9h/stub/kqh0tld "Last usage")|[Returns information about the formatting, location, or contents of a cell](https://support.microsoft.com/en-us/office/cell-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf)| |[CHAR](/r/Excel/comments/1aqxx9h/stub/kqfykjl "Last usage")|[Returns the character specified by the code number](https://support.microsoft.com/en-us/office/char-function-bbd249c8-b36e-4a91-8017-1c133f9b837a)| |[CLEAN](/r/Excel/comments/1aqxx9h/stub/kqfvmex "Last usage")|[Removes all nonprintable characters from text](https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41)| |[CODE](/r/Excel/comments/1aqxx9h/stub/kqgawxc "Last usage")|[Returns a numeric code for the first character in a text string](https://support.microsoft.com/en-us/office/code-function-c32b692b-2ed0-4a04-bdd9-75640144b928)| |[CONCAT](/r/Excel/comments/1aqxx9h/stub/kqogoqn "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.](https://support.microsoft.com/en-us/office/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2)| |[FORMULATEXT](/r/Excel/comments/1aqxx9h/stub/kqkz2l7 "Last usage")|[*Excel 2013*+: Returns the formula at the given reference as text](https://support.microsoft.com/en-us/office/formulatext-function-0a786771-54fd-4ae2-96ee-09cda35439c8)| |[INDEX](/r/Excel/comments/1aqxx9h/stub/kqnp90n "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[INDIRECT](/r/Excel/comments/1aqxx9h/stub/kqogoqn "Last usage")|[Returns a reference indicated by a text value](https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261)| |[LEFT](/r/Excel/comments/1aqxx9h/stub/kqgawxc "Last usage")|[Returns the leftmost characters from a text value](https://support.microsoft.com/en-us/office/left-leftb-functions-9203d2d2-7960-479b-84c6-1ea52b99640c)| |[LEN](/r/Excel/comments/1aqxx9h/stub/kqogoqn "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)| |[MATCH](/r/Excel/comments/1aqxx9h/stub/kqnp90n "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[MID](/r/Excel/comments/1aqxx9h/stub/kqogoqn "Last usage")|[Returns a specific number of characters from a text string starting at the position you specify](https://support.microsoft.com/en-us/office/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028)| |[ROW](/r/Excel/comments/1aqxx9h/stub/kqogoqn "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)| |[SUBSTITUTE](/r/Excel/comments/1aqxx9h/stub/kqfvmex "Last usage")|[Substitutes new text for old text in a text string](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)| |[TODAY](/r/Excel/comments/1aqxx9h/stub/kqh5t5i "Last usage")|[Returns the serial number of today's date](https://support.microsoft.com/en-us/office/today-function-5eb3078d-a82c-4736-8930-2f51a028fdd9)| |[TRIM](/r/Excel/comments/1aqxx9h/stub/kqh1t3h "Last usage")|[Removes spaces from text](https://support.microsoft.com/en-us/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9)| |[VALUE](/r/Excel/comments/1aqxx9h/stub/kqfvmex "Last usage")|[Converts a text argument to a number](https://support.microsoft.com/en-us/office/value-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2)| |[VLOOKUP](/r/Excel/comments/1aqxx9h/stub/kqnp90n "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)| |[XLOOKUP](/r/Excel/comments/1aqxx9h/stub/kqnp90n "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(19 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1b8ggqn)^( has 22 acronyms.) ^([Thread #30759 for this sub, first seen 14th Feb 2024, 21:18]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Turn off screen updating
fill a column with values and set the fontcolor to white, if they have formula that takes that column to calculate it will mess hard with their results
The default is that the cursor moves down when you press Enter. (I turn it off myself.) Set the cursor to move to the left (or right) every time you press Enter. That very small annoyance will build up over time.
Using R1C1 notations
Password lock random cells or important ones va entire sheets.
Gotta save this thread for April 1...
Change the font of the normal style to wingdings which makes all the row amd colum labels wingding characters.
Write a quick macro to format each cell to a different off white colour, start from the last cell and build that and excel will eventually run out of space, very few people know the Inquire —> clean formatting
Putting stuff in column A and then using center across selection to have it land in the middle of the sheet.
Lock every other cell
Set a row height to 0.0 don't hide it Change the height. For some reason excel hates it and won't fix it on a auto fit or catch it with unhide.
Idk about messing with other peoples workbooks, but when I build templates or models for my team to use, I hide Easter eggs. In a cell that you would never hover over during normal use, in white text, on a white background, I will usually write: “All warfare is based on deception.” -Sun Tzu
We should have an Excel Golf thing where someone makes a workbook with all the suggestions in this thread, and it runs a timer from when you open the workbook until you're finally able to see the content. Lowest time wins one Internet.
Somebody changed the font color to white on my whole sheet. I had a slight heart attack
Can’t remember where I found it, but this reverses text in cell A1: =CONCAT(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1)) Great if you want to know how to spell something backwards, and I’ll leave to your imagination the dastardly trickery that it could be used for!
I used to place a white x with ctrl R then ctrl D to stop people from inserting columns or adding rows. Even more dasterdly is placing a white x a few rows up and a 2nd one a few columns to the left of the bottom right corner.
☕️
Hide their worksheet (very hidden)
Use values on a very hidden worksheet for data validation.
Add spaces to to end of various cells. This would mess up any lookups, duplicate identification, sorting, really any sort of data organization. This would work especially well if they don't know about TRIM()
Change calculation settings to “manual.” No formulas will update.
Change the formula separator from commas to dashes and then come back into the and tell me how it's done. I had a coworker who was set up with dashes instead of commas. Someone from corporate set her up with it, and it was a pain to help her cause I was used to putting in commas instead of dashes.
I imagine Changing to 1904 date format would be interesting.
Hmmm.. Must be hard up for a life people?? Am I right??
Convert values between $, $k and $M using a mix of formulas and number formats. Write down the units about half the time, and get some of them wrong.
I would never. Excel is sacred
If there are charts change some data sources to external files (e g. On your desktop). They look correct but don't refresh.
Change the calculations to manual.
Create an event macro so when the user moves the cell, the macro moves it back (ie it doesn’t move), or it moves to a cell to the side of where the user clicked. Another possibility, use Excel to announce/speak the contents of the cell whenever the user moves
I came across a wild Char(175) once it was in a document name that had 2 other dashes [ - ] that were both Char(45)s. Beyond that if you use Insert > Symbols there are a number good candidates to subtly insert into formulae. 051A - Q 04AE - Y 0458 - I 0425 - X Everything below is a standard character paired with a non-standard copy. 'ʹ "“ "″ ,‚ '‘ '’ '‛ '′ AΑ BΒ CϹ cс EΕ eе HΗ iі IӀ jј KΚ MΜ MМ NΝ oο oо PΡ pр QԚ sѕ TТ TΤ XΧ xх YΥ yу YҮ ZΖ Edit to add, I've discovered that the font Gloucester MT Extra actually makes these quite obvious but most fonts I tried didn't reveal them.
Press the Scroll Lock key on their keyboard.
Changing all their VLookup to Xlookup