The Wiert Corner – irregular stream of stuff

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

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

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

    Join 2,087 other followers

Archive for the ‘Excel’ Category

macOS and Windows Excel Shortcut: Toggle absolute and relative references | Exceljet

Posted by jpluimers on 2020/04/20

Since I tend to forget the Mac shortcut (the Windows one feels like it is in my autonomic nervous system):

  • F4    Windows shortcut
  • ⌘T   Mac shortcut 
While editing a formula, this shortcut toggles cell references from relative to absolute, to partially absolute, back to relative again: A1 –> $A$1 –> A$1— > $A1 — > A1 It’s much faster and easier than typing $ characters manually.
To convert an existing formula, enter cell edit mode, place the cursor in or next to the reference you’d like to convert, then use the shortcut.
Note: in Excel 2016 for the Mac, you can also use fn + F4. 

Source: Excel Shortcut: Toggle absolute and relative references | Exceljet

–jeroen

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

Quickly See All the Formulas in Excel with This Shortcut

Posted by jpluimers on 2020/04/13

Toggle between regular view and view all formulas in a worksheet via [WayBack] Quickly See All the Formulas in Excel with This Shortcut which I wish I had known years ago:

  • Windows: Ctrl + ` (the acute accent key, found next to the number 1 on your keyboard).
  • MacOS: Cmd + `

Found using image search macos excel show all formulas – Google Search

–jeroen

Read the rest of this entry »

Posted in Apple, Excel, Mac, Mac OS X / OS X / MacOS, MacBook, MacBook Retina, MacBook-Air, MacBook-Pro, macOS 10.12 Sierra, macOS 10.13 High Sierra, Office, Office 2011 for Mac, Power User | Leave a Comment »

Hightlight active row/column in Excel without using VBA? – Stack Overflow

Posted by jpluimers on 2020/04/02

According to [WayBack] Hightlight active row/column in Excel without using VBA? – Stack Overflow: no, but you do not need much code.

On my list of things to try is to combine both answers there into one.

–jeroen

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

Excel formula: SUMIFS with multiple criteria and OR logic | Exceljet

Posted by jpluimers on 2020/03/31

As I need this one day:

To sum based on multiple criteria using OR logic, you can use the SUMIFS function with an array constant. In the example shown, the formula in H6 is:

=SUM(SUMIFS(E4:E11,D4:D11,{"complete","pending"}))

Source: [WayBackExcel formula: SUMIFS with multiple criteria and OR logic | Exceljet

–jeroen

Posted in Development, Excel, Office, Office 2011 for Mac, Office 2013, Office 2016, Power User, Software Development | Leave a Comment »

Display the relationships between formulas and cells – Excel

Posted by jpluimers on 2020/03/18

The “Formulas” tab has to buttons that help to Display the relationships between formulas and cells – Excel [WayBack]:

  • Precedent cells —   cells that are referred to by a formula in another cell. For example, if cell D10 contains the formula =B5, then cell B5 is a precedent to cell D10.
  • Dependent cells —    these cells contain formulas that refer to other cells. For example, if cell D10 contains the formula =B5, cell D10 is a dependent of cell B5.

To assist you in checking your formulas, you can use the Trace Precedents and Trace Dependentscommands to graphically display and trace the relationships between these cells and formulas with tracer arrows, as shown in this figure.

Worksheet with tracer arrows

Related:

–jeroen

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

 
%d bloggers like this: