Excel is Microsoft’s “killer app”, an indispensable tool for spreadsheet users from all walks of life. Explore the posts below, my human friends, if you seek to unlock the true potential of Excel:
Microsoft Word was, and still is, the “big daddy” of word processing software, and has always been both the talisman and the lightning rod of the MS Office family.. Microsoft has fine-tuned and tweaked Word over the course of its evolution through many iterations, and this evolution has not always met with universal acclaim. In its time, it has inspired more than its fair share of gnashed teeth and bitter tears. Schrodinger’s Cat acknowledges this, but asks that you explore the posts below, and take the time to explore some of Word’s broader horizons and its true potential.
Some of Word’s critics have declared it, in this hyperbolic Trumpian age,to be the “enemy of the people”. The Guardian’s Jason Wilson agrees, Here’s my take.
One very ‘in-demand’ type of calculation in Excel is date arithmetic, which is often an integral part of invoicing or budgeting. You want to display dates that will occur after a given time period in relation to another given date. For example, you might seek to answer a question such as: what will the date be three months from now? And rather than having to manually enter or update the date in question each time, have this calculation built in your spreadsheet via a formula. Here’s how:
Excel’s TODAY function is a simple way of taking today’s date from your computer’s clock and displaying it in a cell in your spreadsheet. All you need to do is type =TODAY() as follows into a cell and press Enter to see the result:
=TODAY()
However, the key consideration is that Excel treats all
date/time values as numbers, for all calculation purposes, which can complicate
your attempt to add another amount to an existing date. For example, if you
want to add 3 months on today’s date, you need to be aware that in Excel, the
basic unit of a date value is an individual day; which means that, simply adding
the number 3 on to today’s date will display the date 3 days from now. Which would be just fine, if that be your
intention. However, to be able to choose which date/time interval to use in
your calculation, you need to incorporate other Excel date functions.
You can use functions such as YEAR, MONTH and DAY to break a date value down to its constituent components, and then add your chosen value to the appropriate component. You can then use another of Excel’s date/time functions, the DATE function, to compile these individual components (year, month and day) into the desired date result. Let’s consider the example below, assuming that cell B3 contains the current date and the adjacent cell C3 contains a number of years. The YEAR function will extract the year from B3 as a separate individual value, and you can then add the required number of years on to this value specifically. In the result cell, you can then incorporate the YEAR function along with the DAY and MONTH function as parts within the DATE function to calculate the desired result, as seen below:
In this way, you can manipulate date values in Excel to
produce the desired type of elapsed time measurements, and this method should
produce this result:
Not as simple and intuitive as it might be, but it works!
Conditional Formatting in Excel can be used in a variety of
ways that can go far beyond simply adding an extra visual emphasis to your
important values. For example, you can
use it to give pictorial expression to the trend created by a data series in a
spreadsheet. This option can be found further down the main conditional
formatting menu, and you can apply it in one of three ways:
Coloured Data Bars
Colour Scales
Icon Sets (symbols)
If, for example, you wanted a visual representation of the differential created by the trend in a series of monthly results; you can do this by applying your choice of Data Bars to the range in question.
The extent and degree of shading in each cell is determined by the value it contains, compared to the other numbers in the same range. The higher numbers in the trend exhibit a much higher degree of shading than do the lower ones. This gives you a method of visually portraying the trend in these numbers, giving your spreadsheet much greater impact than just displaying the bare numbers alone on the screen or page.
If you find that the coloured data bars don’t quite do it for you, there are two other alternatives available on the same menu:
You could go with colour scales, whereby you can represent the same trend with entirely different colours for the higher, lower and in-between numbers. Or you could instead go with Icon Sets and display different symbols such as flags or arrows alongside numbers at different points in the trend. With any of these choices, you can add a crucial extra visual dimension to your spreadsheet’s presentation. Which can be particularly useful if you find yourself reporting to a person who sees many spreadsheets in the average day, so much so that they blend together in one homogeneous Excel-y mass. Conditional formatting is a surefire way of making your spreadsheets stand apart from the crowd!
This assumes ,of course, that you want your spreadsheets to command this sort of attention. And one assumes that you do, at least occasionally, otherwise why would you be reading this post?
Should you wish at a later stage, to edit or modify these conditional formatting rules, you can do so as with any rule – via the Manage Rules option on the Conditional Formatting menu. This dialog box contains both Edit and Delete buttons.
(Bear in mind that you cannot delete conditional formatting via the usual Delete key on your keyboard; this will clear the contents of cells, not the formatting.)
An effective way to that little bit of extra pizz-azz to the
presentation of your Excel spreadsheet is conditional
formatting. This allows you to emphasis important values and make them
stand out visually from the rest of your spreadsheet, as well as making this
characteristic part of the overall design of the spreadsheet, rather than just
doing manual formatting cell-by-cell after the event. You can define and attach
a rule to an entire range, such as a row or column, and thus format whatever
values might occupy that range in a standardised way if they meet your chosen
criteria.
For example, if you have values that you want to measure against a relevant benchmark or threshold, you can then choose a format such as font, bold, italic, font colour, cell colour, border (or any combination thereof) and those values will accept that format whether they occupy that range currently or in the future. You need simply to highlight (select) the range in question, and then go to the Home tab and select Conditional Formatting. If you intend to measure the values against a pre-defined benchmark, the best option is to select Highlight Cell Rules from this menu. The sub-menu for this option allows you to select a comparison (i.e. Greater Than, Less Than, Between etc). For example, if your threshold is 4000, you can then ascribe a format to values that are below this number via the menu on the right. See the example below:
This will give cells that
contain numbers that are less than 4000 a dark red font colour set against a
light red background.
Should you wish to modify
this rule, with either or both a different criteria or format, you can do so
via the Conditional Formatting Rules Manager, which can be found by selecting
Manage Rules… from the foot of the Conditional Formatting menu:
You can modify the rule(s) you have defined via the Edit Rule… button at the top of this dialog box. Note that you can also delete rules via this dialog and create new rules as well, as an alternative method to doing so via the Conditional formatting menu.
You can create as many different rules for the same range as
you wish, and also produce some more elaborate formatting effects by making
full use of the variations available on the Conditional Formatting menu. More
on this in upcoming posts…
One of the more impressive and dynamic feature presentation features in MS Excel is charting. However, in successive versions, the folks at Microsoft have been unable to stop themselves from expanding and complicating Excel’s charting feature to the point where it has become possibly the best exemplar of ‘program bloat’ in the Office family.
For the average Excel user, henceforth, chart creation and modification can be, at best, a time-consuming diversion and, at worst, a confusing and confused labyrinth of excessive choice. There is, happily, good news for user who is time-poor and seeking to adhere to the sacred principles of KISS.
It is possible to generate a useful chart in Excel via just one keystroke. You need only select the range of cells in your spreadsheet containing the labels and values that you wish to portray on your chart. Then, just press the F11 function key on your keyboard and stand back to be amazed!
Excel will instantaneously create a brand new sheet in your workbook, containing a chart based on your selected range of data
A default chart sheet generated in mere seconds with the F11 key
You will immediately have a default 2 dimensional column chart. Of course, this chart might not be be of a type or format that meets your needs; if so, you can then modify your chart by employing the vast and diverse range of tools to be found on the Chart Tools tab that will appear at the top of the screen whenever your chart window is active.
But if the default chart is enough to meet your needs, very possibly the F11 key is all that you will need. A knowledge of this little-known simple Excel maneuver is also likely to impress your colleagues and workmates, who will likely view you with a new respect and, dare one hope, awe.
For more on charting and other Excel presentation strategies, look for upcoming posts…
One crucial but rather underused feature in MS Word is styles. Styles can provide you a powerful and streamlined method of creating and managing the formatting and presentation of your Word documents. And the longer and more elaborate your documents become, the more beneficial and powerful styles can become for you.s
A style is simply a saved set of formatting attributes that you can assign with one click to a variety of different elements within your documents such as paragraphs, individual characters, graphics, tables and bulleted lists. You will find yourself using styles in Word whether you want to or not; the default template that Word employs for each new blank document already has a default style called “Normal”. {Don’t they just love the word ‘Normal’ at Microsoft?) This style dictates the initial appearance of the text you type in a new document- what font, size, colour, alignment et al it has right from the start.
The Normal template also already has a number of different styles readily available to the user. All you need to do is position the cursor in the paragraph you wish to format, and then click on one of the styles that are attached to the panels in the Quick Styles gallery found on the right of the Home tab. The default styles you will see include Normal, Heading 1, Heading 2, Title and many more besides. For a bigger choice, you can use the pull-down menu on the right of the Quick Style gallery to access a more comprehensive gallery of default styles.
You will see that using styles as your chief formatting method gives you two crucial benefits:
Speed: it will give you a rapid, one-click solution to achieve repetitive formatting, particularly in your longer documents.
Standardisation: elements in your document such as headings, paragraphs, tables and lists that employ the same style are guaranteed to look exactly the same. In this way you can effectively create and maintain a professional uniformity throughout your documents, regardless of the ‘whims’ or erroneous formatting choices of other users.
And, furthermore, if you use styles as a foundational concept in your document design, it opens up many other automation features in Word. For example. styles lend themselves to the simple creation of Tables of Contents, the easy enhancement and layout of graphic elements such as diagrams or pictures, and the clever creation of “phone book headers”, to mention but a few. (More on these features in upcoming posts…)
In summary, the use of styles in Word to their full potential is perhaps the best way to differentiate the true “power” Word user from the wanna-be.
The little, green ‘Fill Handle’ in the lower right hand corner of a selected cell in an Excel spreadsheet is a very versatile asset. It provides an easy way of copying the contents of cells, especially formulas, into adjacent cells. It can also automate the creation of common labels such as the names of months and weekdays. One need only enter the initial label (e.g January, Monday), position the mouse over the fill handle in the lower right, and then drag over the required range in the adjacent row or column, and…Voila!
With a simple but clever variation on this technique, you can also easily produce a ‘growth’ series, Just enter the first two values of your intended series, in order to define the increment that you want between the values:
i.e. 20,24…28,32,36,40
Then select both the first two cells, and use the fill handle to create your series with the required growth factor between the successive values.
This method is also ideal for a series of dates. Again, simply enter the first two dates in your intended series, to define the pattern you seek:
i.e. 16/11/18, 23/11/18…30/11/18,7/12/18, 14/12/18
This can be a great way to eliminate much of the tedium of creating rosters, time sheets and the like in your Excel spreadsheets.
Most relatively experienced Excel users are familiar with the use of the Fill Handle, the little green square in the lower right corner of the selected cell, to automate and streamline many routine spreadsheet functions. For example, it provides the user with a simple way to copy formulas into adjacent cells. It is also a great way to “Autofill” a common series of labels, such as weekdays or months. You can just enter the initial label in the first cell (e.g. January, Monday…) position the mouse over the Fill Handle and then drag to the right or down to complete the series as required.
The good news here is that you can “tame” the AutoFill feature to create your own personalised series, and automate its creation in any spreadsheet in the future. Simply create a version of your intended series in consecutive cells in any spreadsheet,and then select (highlight) this range. Go to the File tab and select Options, the last choice on the lower left hand side. Then select the “Advanced” category on the left, and scroll down the right hand screen until you encounter a button bearing the label: “Edit Custom Lists…”
This is the ‘brains’ of the ‘Autofill’ feature. In this dialogue box, you will see your selected range already visible in the “Import list from cells” box on the lower right. If you simply click on the Import button, your list will join the master “list of lists” in the window on the left. You can then create this list via the same AutoFill handle maneuver, and thus significantly accelerate your routine typing process, and earn the undying respect and awe of your workmates and colleagues.
The most recent incarnations of Microsoft’s Office desktop software boast a large and ever-expanding array of commands and features. The enduring problem is: how to find and activate all of these fantabulous features? With enough patience and perseverance you can track them down, hidden away in Microsoft’s typically labyrinthine nest of tabs and ribbons, but all too often this can be a frustrating hit-and-miss endeavour, for it seems the choice of location for many commands doesn’t seem to conform to any recognisable system of logic known to homo sapiens.
Happily, there can be another way for Office users to circumvent Microsoft’s intimidating tabs and ribbons, especially for “old-school” PC users and veterans of the ‘pre-mouse’ era. Most of the commands in your mainstream MS Office program (e.g. Word, Excel, Outlook et al) can be activated via an equivalent keyboard shortcut; many of these are familiar and can be used to the same effect in most MS programs. (for example Ctrl+ C for copy, Ctrl + X for cut, Ctrl + Z for undo). The problem is: how is the average new user supposed to know what keyboard combinations do what?
Fortunately there exists, at least in Microsoft Word, a clever, “hidden” way of easily and instantly generating an exhaustive listing of keyboard shortcuts.
The >Macro> dialogue box in Microsoft Word
Word offers its users a means of automating and standardising routine procedures, via macros that the user can record and then play back as a single action. However, there are also pre-designed macros that are built into Word by default in a library called Word commands. You can locate this library by selecting Macros from the right of the ribbon attached to the View tab. (Why is it found on the View tab? See my earlier observation regarding Microsoft logic.) If you select Word commands from ‘Macros in..’ menu half-way down this dialogue box, you will find one of the macros therein is called “ListCommands”. If you select this macro, and then click on the Run button on the upper right,you will be prompted to create a document that lists all the keyboard settings in Word. This macro will actually a produce a brand new document containing a large table with a comprehensive listing of all of Word’s keyboard settings. You can then readily print out the resulting document, and by so doing, create a resource that might mean that you never need go near a Word tab or ribbon again.
