Posted by jpluimers on 2024/02/13
The conditional formatting feature in Excel is so cool!
If you use FALSE and TRUE expressions to check validity, you can easily make these red and green.
[Wayback/Archive] Conditional formatting of TRUE / FALSE values in an Excel 2010 range – Super User (thanks [Wayback/Archive] tbone for asking and [Wayback/Archive] digitxp for answering):
Posted in Apple, Excel, Mac, Mac OS X / OS X / MacOS, Office, Office 2010, Office 2011 for Mac, Power User, Windows | Leave a Comment »
Posted by jpluimers on 2023/10/23
Remember [Wayback/Archive] Guidelines for human gene nomenclature | Nature Genetics?**
You might not, but this was what pointed me to it back in 2020: [Wayback/Archive] Scientists rename human genes to stop Microsoft Excel from misreading them as dates – The Verge.
The article was a result of Excel mangling import data for decades. Somehow finally it did get Microsoft’s attention and more than 3 years later, they issued options (with mangling still being the default) to help workaround the problems.
The 2004 article [Wayback/Archive] Mistaken Identifiers: Gene name errors can be introduced inadvertently when using Excel in bioinformatics | BMC Bioinformatics | Full Text demonstrated this import problem which had been present for quite a while already (it even has a csh Script to scan for SymbolMutation error).
The gene nomenclature people by now have moved to a different naming scheme, but maybe other people can benefit from the Excel updates of which you can find more through these links:
Posted in CSV, Development, Excel, Office, Power User, Software Development | Leave a Comment »
Posted by jpluimers on 2023/09/23
Long live the Clippy bot!
[Waybacksave/Archive] Elle Cordova on X: “Alexa, Siri and the other bots hanging out in the server break room again”
Posted in AI and ML; Artificial Intelligence & Machine Learning, Bookmarklet, ChatGPT, Development, GPT-3, GPT-4, JavaScript/ECMAScript, Office, Power User, Scripting, Software Development, Web Browsers | Leave a Comment »
Posted by jpluimers on 2023/09/19
Boy, two extra modifier keys: [Wayback/Archive] How to Strip Formatting When You Copy and Paste Text: 5 Ways
To paste as plain text on a Mac, you can use the somewhat cumbersome shortcut
Option+Cmd+Shift+Vto paste without formatting. This is a system-wide shortcut, so unlike Windows, it should work everywhere. Technically, the shortcuts pastes and matches the formatting, but this has the same effect of removing the original formatting.
Via [Wayback/Archive] macos word microsoft office paste without formatting – Google Search.
Paste without formatting is an issue on Windows as well. The default should be “paste without formatting” instead of the current “paste with source formatting”. See for instance these tweets:
Posted in Apple, Classic editor, Development, Gutenberg editor, Mac OS X / OS X / MacOS, Office, Office 2011 for Mac, Power User, Software Development, Web Development, WordPress | Leave a Comment »
Posted by jpluimers on 2023/09/15
This page has a truckload of information, but has a short index and few anchor targets: [Wayback/Archive] Using Fields in Microsoft Word – a Tutorial in the Intermediate Users’ Guide to Microsoft Word.
I found it doing these searches:
I needed them because I misread it at first and replaced { SAVEDATE \* MERGEFORMAT } with { SAVEDATE \* "dd-mm-yyyy" } which resulted in the field to become displayed as needed Error! Unknown switch argument.. and at first thought this might be a Windows versus MacOS thing.
Fixing the error with { SAVEDATE \@ "dd-mm-yyyy" }) almost solved the problem, as the actual format should be { SAVEDATE \@ "dd-MM-yyyy" } (which displays month number instead of minute number).
So I learned that Word date formatting is almost the same as Excel date formatting.
I was specifically looking for these bits (the first luckily has the #Charformat anchor and was immediately followed by the latter):
Posted in Office, Power User, Word | Leave a Comment »
Posted by jpluimers on 2023/08/31
Net op tijd gelukt met de Excel tool van Computer Idee die dit jaar wat lastiger te vinden was dan vorig jaar
Vorig jaar blogde ik dit: Belastingaangifte 2021 met Excel gratis downloaden | Computer Idee
De links van dit jaar:
Posted in Excel, LifeHacker, Office, Power User | Leave a Comment »
Posted by jpluimers on 2023/02/24
Excel on Windows has a neat trick where you can press the Ctrl key after a paste operation.
This lets you determine after pasting if you want to change the [Wayback/Archive] Paste options in a similar way “Paste Special” will modify this while pasting as shown for instance at [Archive] What is the Excel keyboard shortcut for ‘paste special’? – Quora (excluded from the WayBack machine)
- Copy your source (Ctrl + C)
- Press Ctrl + V – Pastes as usual
- Press Ctrl – Paste options get displayed
- Press ‘P’ for paste special /‘V’ for values/’F’ for formulas etc
and [Wayback/Archive] Shortcut in Word or Excel for Special Paste?
Word 2013:
After having copied something go where you want to paste it (without pasting the format). CTRL+V (it will temporarily paste the format too) then CTRL (push and release the control key) then T (the last T means “keep text only”).Excel 2013:
After having copied something go where you want to paste it (without pasting the format). CTRL+V (it will temporarily paste the format too) then CTRL (push and release the control key) then V (the last V means “paste Values”).It’s important that the second CTRL key is released before typing the last letter.This method requires just 4 keyboard hits, no macros and no use of the mouse in a dialog window.
The cool thing about this feature is that you can visually see the original paste, then determine how you want to modify it.
On MacOS, there is the post-paste glyph you can click, but no keyboard shortcut.
Basically, here are three ways I now work on MacOS while pasting:
Command–V: PasteCtrl–Command–V: Paste SpecialCtrl–U, Command–V: Edit cell, PasteOn MacOS, Ctrl–U is the equivalent of F2 on Windows (similarly, Command–T is the equivalent of F4) and brings the cell into edit mode before pasting. The mouse-equivalent is double clicking the cell before pasting.
I got that Ctrl–U / F2 trick from [Wayback/Archive] Always Match Destination Formatting | PCMag via [Wayback/Archive] Excel copy-paste: always match destination formatting – Super User.
All based on [Wayback/Archive] macos excel “paste options” shortcut – Google Search
–jeroen
Posted in Excel, Office, Power User | Leave a Comment »
Posted by jpluimers on 2023/01/25
Given cell A1 is a valid date, I wanted to know the day of that date in that year.
My solution is =A1-DATE(YEAR(A1)-1,12,13)
I disliked the solution in [Wayback/Archive] Day of the Year in Microsoft Excel and [Archive] Day of the Year in Excel (In Easy Steps) (excluded from the WayBack machine), as it is unclear where the + comes from in their solution =A1-DATE(YEAR(A1),1,1)+1
So, here goes my solution, with explanation:
=YEAR(A1) is the year of A1=YEAR(A1)-1 is year before A1=DATE(YEAR(A1)-1,12,13) is the last day of year before A1=A1-DATE(YEAR(A1)-1,12,13) is the day of the year of A1The last step works because subtracting two dates in Excel returns the number of days between those two dates (in a similar way, you can add a number to a date to get a new date number days in the future; similarly you can add time portions as fractions of a day).
The linked solution uses:
=YEAR(A1) is the year of A1=DATE(YEAR(A1),1,1) is the first day of the year of A1=DATE(YEAR(A1),1,1)-1 is the last day of the year before A1=A1-(DATE(YEAR(A1),1,1)-1) is the day of the year of A1=A1-DATE(YEAR(A1),1,1)+1 is a simplification of the day of the the year of A1[Wayback/Archive] excel days from start of year – Google Search
–jeroen
Posted in Excel, Office, Power User | Leave a Comment »
Posted by jpluimers on 2022/12/15
So I wrote a [Wayback/Archive] Thread by @jpluimers on Thread Reader App as I got stuck even before trying to add a Microsoft Account on any on m’y devices Windows 11 or Windows 10. It started with
[Wayback/Archive] Hi @MicrosoftHelps, I have a different problem. When creating an account using Edge on Windows 10 (latest version of both; no plugins; Microsoft Defender in default settings), solving the puzzle loops back to “Please solve the puzzle so we know you’re not a robot.”
The odd thing is that it did not work in either Edge or Chrome (both based on Chromium which uses the Blink browser engine) even in the anonymous/private browsing mode (InPrivate for Edge or Incognito Mode for Chrome).
Even Firefox had trouble, despite it being based on a totally different Gecko browser engine: after solving the puzzle it would continue but instead of displaying success, it showed an error page (the error page also occurred in the Firefox Private Browsing mode):
Posted in Office, Power User, Windows, Windows 10, Windows 11 | Leave a Comment »
Posted by jpluimers on 2022/03/31
Usually, I work in a single worsheet and workbook at a time.
Sometimes I use multiple worksheets or workbooks, and I never knew about the below feature.
The really cool aspect which is not in the below documentation: if you re-open a worksheet, it remembers the views and positions!
Learned a new thing and discovered a new thing (:
[WayBack] View multiple panes, sheets, or workbooks – Excel for Mac:
In Excel for Mac, you can split a sheet in to panes, view multiple sheets in one workbook, or view multiple workbooks at a time.
View multiple sheets in one workbook
- Open the workbook that you want to view.
- On the Window menu, click New Window.
- Switch to the new window, and then click the tab for the sheet that you want to view.Repeat steps 2 and 3 for each sheet that you want to view.
- On the Window menu, click Arrange.
- Do one of the following:
To arrange windows So that they appear like this Click As equally sized, tiled squares Tiled Horizontally from top to bottom Horizontal Vertically from right to left Vertical In an overlapping cascade from top to bottom Cascade - Select the Windows of active workbook check box.
View multiple workbooks
- Open all the workbooks that you want to view.
- On the Window menu, click Arrange.
- Do one of the following:
To arrange windows So that they appear like this Click As equally sized, tiled squares Tiled Horizontally from top to bottom Horizontal Vertically from right to left Vertical In an overlapping cascade from top to bottom Cascade
–jeroen
Posted in Excel, Office, Office 2011 for Mac, Power User | Leave a Comment »
The Wiert Corner - irregular stream of stuff 