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,861 other subscribers

Archive for the ‘Office’ Category

« Previous Entries
Next Entries »

How to quickly add hours/minutes/second to date and time in Excel?

Posted by jpluimers on 2020/11/11

Excel date times are almost identical to OLE/COM automation date times (that used in many Windows applications).

Which means that dates are 1 unit apart, and:

  • Weeks = 7
  • Days = 1
  • Hours = 1/24
  • Minutes = 1/(24*60) or 1/1440
  • Seconds = 1/(24*60*60) or 1/86400

That way you can easily add one hour to a date/time value by adding 1/24.

Source: How to quickly add hours/minutes/second to date and time in Excel?

Related:

–jeroen

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

Add or edit words in a spell check dictionary – Office Support

Posted by jpluimers on 2020/06/26

[WayBack] Add or edit words in a spell check dictionary – Office Support:

By default, the words are stored in a text file called “RoamingCustom.Dic” – Google Search.

You can get there from the menu:

  1. Menu “File”, “Options”, then “Proofing”
  2. In the dialog, click on “Edit Word List…”

–jeroen

 

Posted in Office, Power User | Leave a Comment »

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: [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 »

		


			

			
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 »

		


			

			
worksheet function – How to add or subtract to, or increment, column letters in Excel? – Super User


			
Posted by jpluimers on 2020/03/13


				

					
[WayBack] worksheet function – How to add or subtract to, or increment, column letters in Excel? – Super User:


Here’s the best I’ve found so far: =SUBSTITUTE(ADDRESS(1,( COLUMN() + 1 ),4),1,"")The part in the middle marked in bold is the only part that changes. In this example, it’s taking the current column and adding 1, so returning B if it’s in column A and AA if it’s in column Z.


It is related to the question and answer [WayBack] Formula to return just the Column Letter in excel – Super User:


FYI on your original formula you don’t actually need to call the CELL formula to get row and column you can use:


=ADDRESS(ROW(),COLUMN())



Then as an extension of that you can use MID & SEARCH to find the $ and trim down the output so you are just left with the letter:


=MID(ADDRESS(ROW(),COLUMN()),SEARCH("$",ADDRESS(ROW(),COLUMN()))+1,SEARCH("$",ADDRESS(ROW(),COLUMN()),SEARCH("$",ADDRESS(ROW(),COLUMN()))+1)-2)



edit You can even simplify this further:


=MID(ADDRESS(ROW(),COLUMN()),2,SEARCH("$",ADDRESS(ROW(),COLUMN()),2)-2)





And it is part of a much more elaborate answer


 Read the rest of this entry »

				

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

		


			

			
Error “30029-1001” – Google Search; when installing Office 365 language packs


			
Posted by jpluimers on 2020/01/31


				

					
On my list of things to figure out: Error “30029-1001” – Google Search.


This happens when installing language packs for Office 365.


Related:



–jeroen

				

				
			
Posted in Office, Power User |    Leave a Comment »

		


			

			
Removing “Workbook Contains Macros” Prompt – Free Excel\VBA Help Forum


			
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 »

		


	
	

		
« Previous Entries

		
Next Entries »