The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

    Mastodon

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,854 other subscribers

Archive for the ‘Excel’ Category

« Previous Entries
Next Entries »

Belastingaangifte 2023 met Excel gratis downloaden | Computer Idee

Posted by jpluimers on 2024/08/27

Dit jaar was ik er een paar dagen eerder bij dan vorig jaar: meer rust in mijn hoofd na de verhuizing en een betere planning gemaakt rondom de paardrijvakantie van mijn verstandelijk beperkte broer (waar we altijd zorgen daar in de buurt te zijn zodat we indien nodig snel ter plaatse kunnen handelen).

Waarom is deze blog post zo laat?

Het laat heeft er vooral mee te maken dat deze Excel sheet meestal nog een aantal wijzigingen krijgt na de normale deadline van 1 mei. Dus ik begin pas met downloaden de maand voor de echt harde deadline van 1 september.

Waarom dan toch nu de post?

Eigenlijk is dat heel simpel: vooral zodat je kunt zien hoe je aan deze informatie komt ook al wijzigen de locaties: waar vorig jaar het Excel-bestand voor de belastingaangifte nog bij Google stond, staat het deze keer bij WeTransfer.

Dit jaar zijn de linkjes her en der dus behoorlijk anders: meer dan een simpele vervanging van 2022 door 2023 in de start-link van de eerste Google Search onderaan mijn blog-post.

Let ook op (dit vergat ik vorig jaar te vermelden): dit Excel bestand werkt bij mij niet op Office voor MacOS.

Omdat Computer Idee meestal geen jaartallen in de titels van hun artikelen gebruikt, moet je handmatig de zoekresultaten van Google Search door om te zien welke relevant voor aangifte over afgelopen jaar (in dit geval 2023) is/zijn. Vandaar dat ik hieronder op een aantal plekken jaartellen heb toegevoegd:

Read the rest of this entry »

Posted in Development, Excel, HTML, Office, Power User, Software Development, Web Development | 2 Comments »

GitHub – dabochen/spreadsheet-is-all-you-need: A nanoGPT pipeline packed in a spreadsheet

Posted by jpluimers on 2024/06/12

A great visualisation that LLM are basically a bunch of numbers: [Wayback/Archive] GitHub – dabochen/spreadsheet-is-all-you-need: A nanoGPT pipeline packed in a spreadsheet.

It also shows you that Excel is an excellent tool for working with numbers and formulas on a larger scale.

(note the file is a .numbers file developed in the Mac version of Excel)

Via:

  1. [Wayback/Archive] /Fay-lee-nuh/ on X: “Programmers: Spreadsheets aren’t code @chendabo: Hold my beer”
  2. [Wayback/Archive] Dabo on X: “I recreated an entire GPT architecture in a spreadsheet. It is a nanoGPT designed by @karpathy with about 85000 parameters, small enough to be packed into a spreadsheet file. It is great for learning about how transformer works as it shows all the data and parameters going”

Read the rest of this entry »

Posted in AI and ML; Artificial Intelligence & Machine Learning, Development, Excel, LLM, Office, Power User, Software Development | Comments Off on GitHub – dabochen/spreadsheet-is-all-you-need: A nanoGPT pipeline packed in a spreadsheet

Convert TSV to HTML Table Online | WTOOLS

Posted by jpluimers on 2024/02/20

Great for converting tab separated data (for instance when copied from Excel) into HTML:

[Wayback/Archive] Convert TSV to HTML Table Online | WTOOLS

–jeroen

Posted in Development, Excel, HTML, Office, Power User, Software Development, Web Development | Leave a Comment »

For older Excel versions that do not have the =ISOWEEKNUM(date) function, use =WEEKNUM(date,21)

Posted by jpluimers on 2024/02/14

Calculating an ISO-8601 based WEEKNUM

From [Wayback/Archive] ISOWEEKNUM Function – How to Get the Week Number in Excel:

  • If we are using an older version of MS Excel, we can use the function WEEKNUM. By default, the WEEKNUM function uses an arrangement where Week 1 begins on January 1, and Week 2 begins on the next Sunday (when the return_type argument is omitted, or supplied as 1).
    However, with MS Excel 2010 for Windows and MS Excel 2011 for Mac, we can generate an ISO week number using 21 as the return_type: =WEEKNUM(date,21).
  • There is no built-in worksheet function for ISO weeks before MS Excel 2010.

I tested that ISO-8601 week number calculation in with Excel 2011 on MacOS and Excel 2010 on Windows: the workaround works well for the dates mentioned in ISO week date – Wikipedia.

Read the rest of this entry »

Posted in Excel, Office, Office 2010, Office 2011 for Mac, Power User | Leave a Comment »

Excel 2011/2010: Conditional formatting of TRUE / FALSE values in an Excel range

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):

Read the rest of this entry »

Posted in Apple, Excel, Mac, Mac OS X / OS X / MacOS, Office, Office 2010, Office 2011 for Mac, Power User, Windows | Leave a Comment »

Remember Excel import issues causing a change in Guidelines for human gene nomenclature | Nature Genetics

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:

Read the rest of this entry »

Posted in CSV, Development, Excel, Office, Power User, Software Development | Leave a Comment »

Belastingaangifte 2022 met Excel gratis downloaden | Computer Idee

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:

Read the rest of this entry »

Posted in Excel, LifeHacker, Office, Power User | Leave a Comment »

Excel for MacOS seems to have no post-paste shortcut to modify the paste options

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)

  1. Copy your source (Ctrl + C)
  2. Press Ctrl + V – Pastes as usual
  3. Press Ctrl – Paste options get displayed
  4. 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:

  • When I really want to paste all properties on the ckipboard:
    • Command–V: Paste
  • When I want to decide which properties to copy:
    • Ctrl–Command–V: Paste Special
  • When I want just text:
    • Ctrl–U, Command–V: Edit cell, Paste

On 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.

Related

All based on [Wayback/Archive] macos excel “paste options” shortcut – Google Search

–jeroen

Posted in Excel, Office, Power User | Leave a Comment »

Day of the Year in Microsoft Excel

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 A1

The 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 »

View multiple panes, sheets, or workbooks – Excel for Mac

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

  1. Open the workbook that you want to view.
  2. On the Window menu, click New Window.
  3. 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.
  4. On the Window menu, click Arrange.
  5. Do one of the following:
    To arrange windows So that they appear like this Click
    As equally sized, tiled squares Tiled windows Tiled
    Horizontally from top to bottom Horizontal windows Horizontal
    Vertically from right to left Vertical windows Vertical
    In an overlapping cascade from top to bottom Cascading windows Cascade
  6. Select the Windows of active workbook check box.

View multiple workbooks

  1. Open all the workbooks that you want to view.
  2. On the Window menu, click Arrange.
  3. Do one of the following:
    To arrange windows So that they appear like this Click
    As equally sized, tiled squares Tiled windows Tiled
    Horizontally from top to bottom Horizontal windows Horizontal
    Vertically from right to left Vertical windows Vertical
    In an overlapping cascade from top to bottom Cascading windows Cascade

–jeroen

Posted in Excel, Office, Office 2011 for Mac, Power User | Leave a Comment »

« Previous Entries
Next Entries »