Category: Office

I end up adding lots of words to the dictionary in Microsoft Office; often lots of vendor or product names. I recently added a misspelling of SmartConnect which I needed t remove and I had to do some research to find out how.

It’s not actually too complex to do, but there are a few screens you have to go through.

You can do this in any of the Office products; this example is done through Microsoft Word.

Open the Word Options from the File tab and select Proofing; click the Custom Dictionaries button in the middle of the window:

Continue reading “Remove Word From Microsoft Office Custom Dictionary”

I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

A while ago, I posted an article on generating row umbers in a SQL script; I fielded a question from someone recently on doing the same thing Excel where the row number changes based on two columns of data. Basically when either of the columns were different, the row number should reset back to 1.

The following formula uses an IF combined with an OR to check if either column of data is different to the row above and if so sets the row number to 1 otherwise it increments by 1:

=IF(OR(A2<>A3,B2<>B3),1,C2+1)

A recent update to Microsoft Word saw the page background change from the white which has been used since forever; I’m assuming that I saw this change as I have office set to use the black theme. However, while I want the window black, I don’t want the page background to be black; I want it to continue being white.

There is a button on the action pane, on the View tab which allows you to toggle the view:

Continue reading “Disable Microsoft Word Dark Mode”

I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

I’ve been doing work with quite a few clients with Jet Reports recently and one of the common requirements it to hide a row when values are blank. While the clients I am working with are experienced with Excel, they often are very good with VLOOKUP and other functions, but not so much with AND/OR operators.

This can be done using an IF statement combined with an AND operator. In Excel the and statement format is different to programming/scripting languages.

The below is an example AND checking the value in cell A2 and B2 and returning true if they are both 1 otherwise returning false:

=IF(AND(A2=1,B2=1),"True","False")

Just for completeness, the below is an example OR checking the value in cell A2 and B2 and returning true if they either cell is 1 otherwise returning false:

=IF(OR(A2=1,B2=1),"True","False")

I might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

I do far more manipulation of data in SQL scripts than I do in Excel, but sometimes need to accomplish something in Excel for clients. This time I was working with a client using Excel to generate a Microsoft Dynamics GP macro to update some costs.

We realised when testing that the decimal places were being lost by Excel so the macro contained 100 for £100 instead of the 100.00 which is needed for the macro to run successfully. I tired a couple of things and eventually found that TEXT can be used to retain the decimals.

The example, below, also includes a ROUND to reduce the result of a calculation to only two decimals.

=TEXT(ROUND(B2,2),".00")

My knowledge of using Excel is somewhat patchy; there are some things I can do easily and other simple things which I find troublesome. While I might not post many Excel snippets, I’m collecting them into a small series to make them easy to find again in future.

I’ve already been doing similar posts with Network Shell Snippets, PowerShell Snippets and VBA Snippets. The series index will automatically update as posts go live, but if you’re reading a syndicated version, you’ll need to check back to the original post

Excel Snippets
Get first day and last day of month using an Excel formula
Substitute Function
Preserve Decimals in Result of Formula
AND/OR Operators
Generate Row Numbers
Calculate Difference Between Dates
Add n Months to Current Date
Formula to Calculate Monthly Value of a Mortgage with Monthly Interest
Pad Numbers with Leading Zeroes
Offset Formula to Calculate Range on Number
Get First and Last Dates of Calendar Year
Get First and Last Dates of UK Tax Year
Formula to Zero Pad to 6 Digits

We recently had anew consultant join us at ISC Software which meant building a new laptop. As she lives fairly near me the laptop and other kit was delivered to me and I did some setup. Once she was logged on there was one unexpected issue with emails not sending in Outlook.

I did some checking of settings and found that there is a setting which needed to be enabled for sending of emails; I’m not sure why it was not set as I don’t recall having set it previously.

The setting is in (File » Options); select the Advanced tab and, under Send and receive, mark the Send immediately when connected checkbox:

Once the options were saved, emails were correctly sending.

PowerPoint is not something I have used very often, but I am now doing webinars on at least a monthly basis at work. I’ve inherited some PowerPoint slidedecks from other people with some features I don’t like; one of those is having to click to display elements on slides. This is something which I now know to be animations and their removal is actually quite straightforward.

To remove an animation, select the Animations tab and then select element with animations; the Animation section of the action pane will show the type of animation currently selected; click None to remove the animation: