 | Lars Fosdal on Security alarm provider Woonve… |
 | Thomas Mueller on Question got closed in May 202… |
 | Thaddy de Koning on Formulier voor bewindvoerders… |
| Thaddy de Koning on Formulier voor bewindvoerders… |
| Thaddy de Koning on Formulier voor bewindvoerders… |
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:
Source: [WayBack] Excel 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 »
Posted by jpluimers on 2019/11/22
This indeed was an Excel 2011 for Mac thing.
Even without macros or VBA modules, Excel 2011 for Mac shows this dialog when opening a .xls file.
The solution was simple: save as .xlsx.
–jeroen
via [WayBack] Removing “Workbook Contains Macros” Prompt – Free Excel\VBA Help Forum
Posted in Excel, Office, Office 2011 for Mac, Power User | Leave a Comment »
Posted by jpluimers on 2019/11/11
Lots of fuzz, but the formula towards the end worked:
=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)
Source: [Archive.is] How to Extract a Number or Text from Excel with this Function
More of those at [WayBack] Extract Only Numbers From Text String
–jeroen
Posted in Excel, Office, Office 2011 for Mac, Power User | Leave a Comment »
Posted by jpluimers on 2019/11/08
Why does it take forever for Microsoft Office suite programs to function the same across their various components and over operating systems. It has been like 2 decades and still sizing columns/rows in tables is a nightmare.
[WayBack] Resize a table in Word or PowerPoint for Mac – Office Support.
In my case PowerPoint: it is so different from Excel, and even different from Word, that it gives me headaches.
This is what PowerPoint 2011 could do; more recent versions are only marginally better:
PowerPoint
You can resize a whole table to improve readability or to improve the visual effect of your document. You can also resize one or more rows, columns, or individual cells in a table.
Do any of the following:
Resize a table
- Click the table.
- Rest the pointer on any corner of the table until
appears, and then drag the table boundary until the table is the size that you want.
Change the row height in a table
- Rest the pointer on the row boundary that you want to move until
appears, and then drag the boundary until the row is the height that you want.
If you have text in a table cell, the row must be the same height or taller than the text.
Change the column width in a table
- Rest the pointer on the column boundary that you want to move until
appears, and then drag the boundary until the column is as wide as you want.
If you have text in a table cell, the column must be as wide as or wider than the text.
Change the row or column to fit the text
- Rest the pointer on the column boundary until
Make multiple rows or columns the same size
- Select the columns or rows that you want to make the same size, and then click the Table Layout tab.
- Under Cells, click Distribute Rows or Distribute Columns.
–jeroen
Posted in Office, Office 2011 for Mac, Power Point, Power User, Word | Leave a Comment »
Posted by jpluimers on 2019/08/16
Since I always get confused by the differences in Excel versions (not just between Mac OS X and Windows):
In Excel for Mac, you can adjust where automatic page breaks occur, add your own page breaks manually, and remove manual page breaks.
Source: [WayBack] Insert, move, or delete page breaks in a sheet.
–jeroen
Posted in Excel, Office, Office 2011 for Mac, Power User | Leave a Comment »
Posted by jpluimers on 2019/06/28
I hardly print with Excel, so I always forget these two:
–jeroen
Posted in Excel, Office, Office 2011 for Mac, Office 2013, Power User | Leave a Comment »
Posted by jpluimers on 2019/06/10
Via [WayBack] ms office – Keyboard shortcut to select all text in a cell in Excel – Ask Different a few keyboard tips.
Lets start with the shortest one:
Command–Z or Control–Z to undo the changeThis probably is unintended, but works great: all text is now selected, so you can copy/cut with Command-C/Command–X.
Now the “official” way:
Control–U or F2 to edit the cell (the cursor is now at the end)Shift–Alt–Home or Shift–Control–Home (to select all text)Home can also be Fn–Left.Other selections you can make while the cell is in edit mode:
Shift–Alt–End or Shift–Control–End (to select to the end of the cell)End can also be Fn–Right.Shift–Alt–Right or Shift–Control–Right (to select one word to the right)Shift–Alt–Left or Shift–Control–Left (to select one word to the left)Shift–Alt–Down or Shift–Control–Down (to select to the same position on the line below)Shift–Alt–Up or Shift–Control–Up (to select to the same position on the line up)Keyboard symbols (more at [WayBack] Command, Option, & Shift Symbols in Unicode):
⇧ – Shift^ – Control⌥ – Alt which is the same as Option⌘ – CommandFn – Function–jeroen
Posted in Apple, Excel, Mac, Mac OS X / OS X / MacOS, MacBook, MacBook Retina, MacBook-Air, MacBook-Pro, MacMini, macOS 10.12 Sierra, Office, Office 2011 for Mac, Power User | Leave a Comment »
Posted by jpluimers on 2019/06/03
What key combination do I have to press to create a line break in a cell in Excel for Mac 2011? The Windows combination of Alt+Enter does not work on the Mac.
Source: [WayBack] keyboard – How to add a line break in a cell in Excel for Mac – Ask Different
The answer depends on the Excel for Mac OS X version you are using.
Excel 2015 is simple (thanks esham): use Option+Enter.
In older Excel <= 2011 (thanks nwinkler), use Command+Option+Enter or Control+Option+Enter.
Some users report the also need the Fn key in addition to the above modifiers.
–jeroen
PS: Later I found out that [WayBack] Beckism.com: Use a linebreak in Excel on Mac also shows the Excel <= 2011 solution Control+Option+Return (note that Return is the same key as Enter).
Posted in Apple, Excel, Mac, Mac OS X / OS X / MacOS, MacBook, MacBook Retina, MacBook-Air, MacBook-Pro, macOS 10.12 Sierra, Office, Office 2011 for Mac, Power User | Leave a Comment »
Posted by jpluimers on 2018/08/24
Based on [WayBack] MS Excel 2011 for Mac: How to Change Data Source for a Pivot Table:
The last trick makes it way easier to add newer data from an external CSV file into an existing workbook with various PivotTable analysis worksheets:
On a 1920×1200 screen, the PivotTable toolbar looks like this:
Excel 2011 for Mac PivotTable toolbar
–jeroen
Posted in Office, Office 2011 for Mac, Power User | Leave a Comment »
Posted by jpluimers on 2018/03/27
From the “I hate my users” department:
–jeroen
Posted in Apple, Development, iMac, Mac, Mac OS X / OS X / MacOS, MacBook, MacBook Retina, MacBook-Air, MacBook-Pro, MacMini, Office, Office 2011 for Mac, Power User, Software Development, Usability, User Experience (ux) | Leave a Comment »
The Wiert Corner - irregular stream of stuff 