Microsoft Excel – gridlines lost when formatting cell background

When you add colour to a cell background in Excel, either manually using the Cell Background  Cell background icon button or by using Conditional Formatting, you lose the gridline round the cell.

Cell gridlines

 

If you really want to see the gridline, you can reproduce it using the cell borders button. Select the relevant cells, then from the Borders dropdown, select All Borders, then click More Borders. The Format Cells dialog box will be displayed with the Borders tab uppermost. From the Color dropdown, choose White, Background 1, Darker 15%. On the right-hand side, click on all borders of the rectangle in the Border section.

Format Cells Borders dialog box

Click OK.

You will now see gridlines round your data.

Cell gridlines

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

 

Advertisements
Posted in Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016, Uncategorized | Tagged , , | Leave a comment

Microsoft Word – getting back to normal text after using text effects

If you use the Text Effects  Text Effects icon button in the Font group on the Home tab, you produce some interesting effects. However, it is not then obvious how to get back to normal writing!

To do so, click the Clear All Formatting Clear All Formatting icon button also in the Font group on the Home tab. This will get you back to ordinary type.

Find out more about our Microsoft Word training in the London area or our Microsoft Word training in Dorset, Wiltshire and Hampshire.

Posted in Microsoft Word, Microsoft Word 2010, Microsoft Word 2013, Microsoft Word 2016 | Tagged , , | Leave a comment

Microsoft Excel – Sorting by colour and within that alphabetically

If you conditionally format data in Excel, you can sort by colour.

Conditionally formatted data  In the above data set, I have conditionally formatted all those with word ESTATE in the description.

Then, on the Data tab, I click the big Sort button and enter as below.

Sort dialog box

Then click Add Level and select Description again, this time with Order A to Z.

Sort Dialog box level 2

The data will be sorted as required.

Sorted by colour and alphabetically

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

Posted in Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016, Uncategorized | Tagged , | Leave a comment

Microsoft Excel – using series from more than one worksheet in a chart

Sometimes you may create a chart in Excel and then want to add data from another worksheet.

Excel chart

To add in extra data, on the Chart Tools Design tab, in the Data group, click Select Data.

Change series dialog box

At the left under Legend Entries (Series), click Add. The Edit Series dialog box will be displayed.

Edit Series dialog box

Locate the data you want to add. From the Series name box, click in the cell containing the label of the series you want to add. From the Series values box, drag across the cell range containing the values you want to add. Click OK twice. The data will be added to your chart.

Chart with series added

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

Posted in Microsoft Excel, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged , , | Leave a comment

Microsoft Excel – date format of Monday 1 Jan 2018

I was asked whether I could create a date format of Monday 1 Jan 2018 and copy it down to Tuesday 2 Jan 2018 etc. This requires a custom format. However, I also found it required me changing my computer settings for Long Date in Date/ Time settings.

Access the Date/Time settings on your computer and find the setting for changing date and time formats – this will depend on which version of Windows you are using. For the Long Date, select dddd, d MMMM yyyy as this is the nearest to that required.

Now in Excel, select the cells to be formatted, and open the Format Cells dialog box at the Number tab. Select Long Date. Then click Custom. Adapt the setting to be dddd d mmm yyyy. Click OK.

Type into the first cell 01/01/2018 – it will be displayed as Monday 1 Jan 2018. You can then use the Autofill handle to fill in the rest of the dates.

dddd – shows day of month in full

d – shows 1st digit of day

mmm – shows month as Jan, Feb, etc.

yyyy – shows year in full

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset

Posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016, Uncategorized | Tagged , | Leave a comment

Microsoft Excel – quick entry of last day of every month

To enter the last day of every month into a column in a worksheet, first enter the last date of the first month to be included, say 31/01/2017.

Drag down to fill in as many as required. Go to the Autofill Options button at the bottom and choose Fill Months. The last date of each month will be entered as required.

Last day of each month

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

Posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged , , | Leave a comment

Microsoft Excel – adding an “X” or equivalent text in several cells in a column

I was asked recently how one could add X to every number in a cell – this would be for a product code, a part number etc.

Part numbers

This is a matter of formatting the data. Select the cells you want formatting. On the Home tab, in the Number group, click the little dialog box launcher arrow . The Format Cells dialog box will be displayed with the Number tab uppermost.

Format Cells Number tab

Click Custom. In the box to the right, choose the format that is nearest to the one you already have – in this case #,##0.

Then in the Type box adjust to what you need, in this case ###0X.

Format Cells Custom Number

Click OK. Your column will be formatted as required.

X added to numbers

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

Posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged , | Leave a comment

Microsoft Project – changing text size on Gantt Chart

If you have text on top of Gantt bars, it can sometimes take up a lot of space and make each row quite deep. To change the text size on the Gantt chart, on the Gantt Chart Tools Format tab, in the Format group, click Text Styles.

Text Styles dialog box

From the Item to Change drop down, select the relevant bar text to change – Left, Right, Top, Bottom or Inside.

From the Size box, select your required font size.

Click OK.

Discover more about our Microsoft Project training in the London area or our Microsoft Project training in Dorset, Wiltshire and Hampshire.

Posted in Microsoft Project, Microsoft Project 2010, Microsoft Project 2013, Microsoft Project 2016 | Tagged , | Leave a comment

Microsoft Excel – handling a mix of calendar days and business days

Someone I was training recently had been given a spreadsheet with dates from the beginning of the month – some as calendar dates – ending is st, nd, rd, th and some with no suffix which were business days from the beginning of the month. He wanted to be able to convert all of these into actual dates.

Days

In cell C1, I entered the first date of the month, in this case, 1/11/2017.

May be if I sit down and look at it for longer, I could do this all in one formula, but I did it in three stages.

In cell C4, I entered the formula

=IF(LEN(B4<=2),WORKDAY($C$1,B4),””)

This looks at what was in cell B4 and if it didn’t have a suffix, used the WORKDAY function to work out the date in cell B4. I then copied down the formula to the rest of the dates. This then gave me a date for all those with business days and an error message for those with calendar days.

Days

In column D4, I entered the formula

=IF(LEN(B4)>2,LEFT(B4,LEN(B4)-2)&”/”&MONTH($C$1)&”/”&YEAR($C$1),””)

This looks at the length of the value in cell B4. If it is a calendar date, it then gets rid of  the suffix by using the LEFT function, combined with the LEN function (which looks at the length of the string) to retrieve just the day and concatenates it with the month and year of the first of the month in cell C1.

Days

I then need to combine these into one column. In cell D4, I used the following formula.

=IFERROR(C4,DATEVALUE(D4))

This returns the value of C4 if there is a date there, otherwise it returns the value of D4. Also ensure that the column is formatted as Date.

Days

Columns C and D could now be hidden.

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

Posted in Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged , , | Leave a comment

Microsoft Excel – keep sort details when creating sorts on more than one set of data

A client had a tennis scoring spreadsheet with different sets of data for different divisions on the same worksheet. Players on equal points are thereafter ranked on best games difference, then games won and then head to head.

Whenever he created a sort, then sorted another division, the sort order of the previous division was not remembered and if the data changed, he had to re-input the sort criteria.

This is a useful example of when it is worth formatting data as a table. I created a different table for each division by selecting the relevant data, then on the Home tab, in the Styles group, from the Format as Table dropdown, selecting a table format I like.

Tennis sort

Then for each table, on the Data tab, click the big Sort icon.

Select the required sort order, then click OK.

When you revisit any of the tables and click Data – Sort again, the required sort order will be kept and clicking OK will re-sort the table.

Find out more about our Microsoft Excel training London, UK.

Find out more about our Microsoft Excel training Wiltshire, Hampshire, Dorset.

Posted in Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 | Tagged , , | Leave a comment