Excel Formatting

Quick Formatting

I use-to spend a lot of time formatting the background colour of headings then making the text white. Changing the font and size and then trying to remember what my style was to apply to other Excel workbooks because I like to keep my formatting consistent with my business branding.

This process was taking me for ever and going back and forth finding Excel workbooks that had the formatting I liked or finding the notes where I had written the style attributes, was just becoming a nightmare until I noticed Cell Styles.

Yes, it was right in front of me for a long time. Cell Styles are found under the home tab.

  1. Select relevant cells
  2. Home tab
  3. Select Cell styles
  4. Choose the colour from the list
  5. You can even create your own style
cell-styles

Wrap Text

Wrap text is when you want to keep the column width at a specified size. It’s great for comments or notes. Start by setting the column to the width you would like by Dragging from between the column letters, your mouse pointer will change to a double headed arrow. Select the cell to do the wrapping on. Go to the Home Tab select Wrap Text.

You might like to use a dash – or an Asterix * to represent a bullet point in your comments field, use wrap text and Alt Enter to keep the text in the same cell but in the next line down.

PC Users: Press Alt Enter.

Mac users: Press Option Command Enter.

column width
wrap text
comment box

Widen Columns

There are several techniques you can use to widen the columns in Microsoft Excel. Try double click between the column letters. Ensure you are getting a mouse pointer with a skinny little cross not the down arrow that will select the entire column.

When widening a column to a specific width, keep in mind the width is in characters and not in cm, mm or pixels. Position your mouse pointer between the column letters until you have the skinny little cross and then drag you will see the numbers display.

Are you the type of person who knows what they want and how they want to see it, try Right Mouse Click instead and select column width, type the no of characters wide you would like for example 20. The Column width will now be 20 characters wide.

Format Painter

We spend a lot of time making our Excel spreadsheets look good. At times someone else has done the hard work picking the correct colour, font, size etc and all you may be wanting to do is to re-apply exactly what they have done.

First select the text that already has the formatting you want, go to the Home Tab and select “Format Painter“. This is where most people say, yeh, yeh I know what to do.

Well I bet you pressed the Format Painter BUT only ONCE. Ok, try this click on the Format Painter TWICE, get trigger happy (DOUBLE CLICK) instead. The Format Painter will now stay on so you can apply to other text. When you would like to stop press the Format Painter again or press ESC.

Format As Table

Format as Table feature is not just pretty colours data is kept together as you enter it. This feature turns on the Freeze Pane, Filters, Colours for every second row and keeps your total together.

Found under the Home tab next to Conditional Formatting. It’s great with Pivot Tables if your data is continuously being added to. You could also use this feature in functions like the table component of a VLOOKUP Function.

The Default name for a table is “Table1” with no spaces in its name but you can change this to anything you like so long as there are not spaces in the name. Table1 is usable just like Range Names in functions.

New Records are created by pressing TAB on the last cell of the last record without disturbing your summary total. Records and column headings grow, keeping together as a database, making it a dynamic data set to use with Pivot Tables and charts. Saves the need for creating an Offset function. Ensure there are no blank columns or rows.

Tip: If you need to use Subtotalling you must turn off the Table Feature. Press Convert To Range.

  1. Ensure your cursor is in the data set
  2. Home Tab
  3. Format As Table
  4. Choose a Colour

Notice as you scroll down your column headers change from letters to what you had in the top row of your data set.

If this is not working for you then you have not selected a cell inside the data set and you will not be seeing the tab for Table Tools. Any formatting from before due to conditional formatting will not be affected.

If the colours are not showing according to what you selected, then select the cells and clear the formatting.

Clear formatting can be found in the Home tab on the far right.

Format Table For Mac

The Macintosh also has the feature of Format Table which has the benefits of:

  • Freeze pane
  • Sort/ Filter
  • Totals Row
  • Alternate row colours
  • Data extending as you type in new Rows and Columns.

What’s different is naming your table. Whilst on a PC on the top left you have “Table1″ name, almost above the name box. The Mac does not.

For this on Mac you will need to Press the button for Rename (even though you haven’t named it yet.). Now give it a name.

format as table formatting

Conditional Formatting

Conditional Formatting can be used to show you your standout sales reps or detect when stock is low. Help you pin point problem areas in the business using colour, symbols and graphs. It’s nice to know when you have reached your target with a splash of colour.

  1. Select your cells to format with colour
  2. Home Tab
  3. Conditional Formatting
  4. Select highlight Cells Rules
  5. Greater Than
  6. Enter eg 300 and Select the formatting
conditional formatting
format cells to be greater than
conditional formatting

Highlighting Duplicate Values

Make duplicate values stand out. Check invoice numbers, Order numbers, Product codes don’t appear twice in your master list.

  1. Select the cells to change in colour
  2. Home tab
  3. Conditional Formatting
  4. Highlight Cells Rules
  5. Duplicate Values
  6. Choose your colour options
  7. Note you can also select Unique values
format duplicate values
format duplicate values results

Icons And Sets Conditional Formatting

It can be handing to display symbols to let you know if figures are higher or lower than a benchmark amount. When you use this feature, you will need to display a legend just like in graphs.

  1. Select the Figures
  2. Home Tab
  3. Conditional Formatting
  4. Icons and Sets
  5. Choose your icons
  6. Ensure you are still in at least one of the entries with an icon
  7. Home Tab
  8. Conditional Formatting
  9. Manage Rules
  10. Edit Rules
  11. Press Alt Print Screen to take a snap shot of only the open dialog box
  12. Then Crop
conditional format icons and sets
icons and sets arrows
icons legend conditional formatting

Remove Duplicated Data

Removing duplicates use to be hard work. You would have sorted data, created an IF function to test the row above to the one below and return Yes or No.

Well forget all that hard work all you need to do now is press a button make a few selections and you are done and dusted. Where is that magic button you ask – tune in next week for the answer, just kidding.

If you don’t quite need to remove duplicates but need to know if there are any and where in the lists they are, then use Conditional Formatting to highlight the duplicates.

Sometimes we may have a list of, Account codes, Security pass code, Surnames or Departments where there should be no duplicates at all. Follow these steps to remove the duplicates.

  1. Select the cells with the duplicate values
  2. Go to Data tab
  3. Select Remove Duplicates
remove duplicates

Custom Views

Custom view is a very useful tool when you find yourself changing the setting for printing your spread sheet, needing to change the page orientation, margins, page orientation, setting print area. Each sheet could have different needs. Follow the steps below

Option 1: Print all the data

  1. Go to the View Tab
  2. Select Custom View
  3. Press the Add button
  4. Type the Name all
format worksheets custom view
create names for custom views

Option 2: Print only the Quarter

  1. Set your spreadsheet up with Grouping
  2. Select row 6 7 and 8 (the rows you want to hide)
  3. Go to Data
  4. Select Group
  5. Repeat for column or rows to hide
  6. Collapse and Expand your levels by pressing the + and – sign
  7. Change any print settings – margins, page orientation
  8. Now – Follow the steps in Option 1 and name it, Quarter
create names for custom view format
collapsed outline view format

Option 3: Print only the Grand Totals

  1. Collapse your levels
  2. Change any print setting – margins, page orientation
  3. Now – Follow the steps in Option 1 and name it, Grand Totals.

Fix Custom View Greyed Out

Items on the Ribbon become greyed out when you are in Edit mode in a cell. If you can see your cursor flashing in a cell, next to text or numbers you entered. The Ribbon will be greyed out. If you clicked in the formula bar, you are also in Edit mode.

If you look at the bottom left corner it will tell you what mode, you are in. Simply press Enter to exit this mode. If you press Ctrl Enter you will exit the mode but stay in the same cell ready to perform your formatting.

When you use added features like a graph, Pivot Table, Table or Slicers you will notice an extra tab in the Ribbon specific to this task and at times it may be disabled from using one feature while another is turned on.

There could be a few things going on sheets could be grouped or you could have the Format As Table feature turned on. If your sheets are grouped this will automatically disabling certain features in the Ribbon.

To Ungroup Sheets

Right Mouse Click on a sheet and Select Ungroup sheets. If that hasn’t solved your problem perhaps you have Tables active.

To Turn Off Format As Table

Ensure you are in the table data. You will see in the Ribbon a Table Tools tab, select Design and press Convert to Range. If Custom view is greyed out and there are no tables or sheets that are grouped.

Copy To New Workbook

Copy and paste the problem sheet to a new workbook. This usually clears out any underlying issues.

Mac users also have the feature of Format Table although some features are a little different visit my blog on Format Table for Mac.

Improved Symbols For Office 365

You can now insert your favourite icons like Disabled Icon, Toilet, Printer, Mobile, Finger Print Icon, Bed, Bin, Magnifier, Wi-Fi, Sound, Play, Battery Charging, Drop Pin, Lock, Hazards, Screen People, Party icons and much more into your Word, Excel, PowerPoint software packages.

They initially come in Black, but you can change them to whatever colours you like. Convert icons into SVG (Scalable Vector Graphics) – English ungroup them and change the colour of individual parts. Symbols command button remains, this looks to be an extension of Symbols.

  1. Open Excel or Word
  2. Go to Insert
  3. Select Icons
  4. Choose from the list and Insert
  5. From the Graphics Tool tab
  6. Select Format
  7. Change colours or Convert to Shape to be able to change individual components.
icons
icon categories
graphics

Conclusions

Microsoft Excel has many interesting formatting tools apart from the basic Font, Size, Colour. I find the Format A Table Feature is often under utilised. Did you know that this feature is much more than a glorified pretty colours.

It turns on many features like Freeze Pane, Sort and Filter, Colours every second row, turns on a Total row for summing, adding or averaging. The best part is that it makes the data dynamic. In Conditional Formatting you can create formulas for your criteria to then apply colour.

Microsoft Office Small Group Training Sessions

AZ Solutions delivers customized training courses in Sydney – Australia. We come to you. All you need is a board room, PC’s for each student and a TV/ Projector with a HDMI connection cable.

In our training sessions you are welcomed to bring examples of your work to class. We prefer it.

Call Now M 0414 417 059 visit www.azsolutions.com.au