Tag Archives: Viewing

A little bit of history – lost … and found

Excel has been around for decades, so it isn’t surprising that there are many features tucked away under the hood. What is surprising is when a useful feature is lost and only careful archeology can bring it back to life.

Excel 4.0 had a rudimentary macro language, mostly using excel formula approaches to building functionality. This was replaced by the VBA programming language. But there are still useful little items tucked away in this early language that haven’t been replaced.

One of these is Get.Cell.

Get.Cell had a boatload of switches that allowed the user to pull information about the cell formatting and contents and most of these have been replaced by the Cell and Type functions in Excel.

But one piece of information that Cell and Type can’t tell you is whether your cell or cells contain formulas vs values and sometimes this is a very handy thing to know at a glance. For example, if you build a spreadsheet using formulas to estimate amounts; but then start to drop in values as more concrete information becomes available.

In this situation I like to format cells containing formulas differently from the cells containing values, so that I can see at a glance where my estimates are. Its’ handy to have the formatting change automatically, so I don’t have to remember what my rules are weeks or months later.

This is where Get.Cell shines. The syntax I’m going to use is

=GET.CELL(48,A1) – where A1 is the cell I’m going to reference.

The trick here is that Get.Cell is NOT entered in a cell, but instead as a named formula. After creating the named formula, I can reference it while applying conditional formatting. In this way, when the type of content in the cell changes the conditional formatting automatically updates.

Using a named formula in Conditional Formatting - dialog box
Using a named formula in Conditional Formatting

Where does one find information about the Get.Cell function? Not from Microsoft or at least not easily from Microsoft.

Try this post https://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html   to see the possible switches for Get.Cell

 

 

PowerPoint 2016 – a change in Guides

I noticed this improvement to guides in PowerPoint 2016 the other day.

If you apply your guides while in Master View, you can’t inadvertently move them while in Normal View. This is great! And consistent with the way objects behave between normal and master views.

By the way – guides applied in Normal View, can still be moved around in normal view. Like I said – consistent!

iOS 10, Accessibility and Vision Options

If you are using an iPhone or iPad and your vision is compromised (or, ahem if you are over 50), you might find these options useful. With every version of iOS the accessibility options have improved and expanded.

The following is the sequence of steps I follow when setting up an iOS device for someone with vision issues; as always your mileage may vary, but even if you only catch yourself squinting at your phone from time to time – give these options a try.

Change your Wallpaper to Black

It is cool to have nifty images on your phone screen, but if you are having trouble reading the labels for your folders, then it may be time to use a black background. You may also want to do this for your lock screen so that notifications and buttons stand out more.

The easiest method is to take a black photograph and use it as your background. Set your device on a table, block the light and take a photo (you may need to turn off your flash first). Use this photo for your background by going to Settings>Wallpaper > Choose a New Wallpaper and selecting your new photo from the Camera Roll.

Setting or Cancelling the change of Wallpaper
Setting or Cancelling the change of Wallpaper

Choose the Set option and then select the Set Home Screen option

Setting the Picture to display on the Lock or Home Screen
Setting the Picture to display on the Lock or Home Screen

Improve Your View

Next go to Settings > General > Accessibility and select the following options:

  • Larger Text, you can start by using the slider to set your preferred text size or you make things even BIGGER by turning on Larger Accessibility Sizes and using the slider again. This comes with a couple of caveats. 1) Text is not affected everywhere on the device – tho Apple is clearly working to change this, and third party apps are (slowly) coming around. 2) Sometimes, you can have too much of a good thing and have text that throws off the formatting or wraps weirdly or overlaps illegibly. Again, these problems are gradually being worked on as apps are updated.

    Setting the Larger Text size
    Setting the Larger Text size
  • Bold Text – turning this option on will require a reboot of your device, because EVERYTHING has to be redrawn.  But this will make the typeface used far more readable.
  • Button Shapes – turning on button shapes will make them easier to find. Now, if they were only used more consistently throughout the OS, that would be awesome.
  • Increase Contrast and turn on Reduce Transparency and Darken Colors.  This should also help text stand out more.

    Increase Contrast options
    Increase Contrast options
  • On/Off Labels add the | 0 symbols to your toggles, and I say why not!
Accessibility Options
Accessibility Options

Use your Home button to Zoom IN

While you are still in Settings > General > Accessibility , move down to Accessibility Shortcut. Here you can customize the action of the Home button when your triple-click it.  Choose Zoom

Turning on Triple-Click to Zoom
Turning on Triple-Click to Zoom

You’ll need to fine-tune Zoom’s behaviour, by going to Settings > General > Accessibility  > Zoom

Zoom Options pt 1
Zoom Options pt 1

Don’t worry about turning Zoom on here, that is what your triple-click will do. Instead:

  • Turn on Follow Focus and Smart Typing.
  • You may choose to Show Controller, but I find there is a balance between clutter and helpfulness, for me this clutters the screen too much.

    Zoom Options Part 2
    Zoom Options Part 2
  • Select Zoom Region and choose Window Zoom.
  • Play with the Maximum Zoom Level slider, you’ll probably adjust this more later.

To turn the zoom window on triple-click your home button and the zoom window will appear, outlined in black with a small tab handle at the bottom.

The Zoom Window in action
The Zoom Window in action

This is a toggle, so a second triple click turns the zoom window off.  And what is really great, is that you can interact with the screen through the window.  Click on links and type in dialog boxes whatever you need to do. This window is not just for viewing!

Customize the Zoom Window

By touching the tab handle shape at the bottom of the zoom window, a customization menu opens up.

Changing the Zoom Window options
Changing the Zoom Window options

Through it you can:

  • Zoom Out (turn off window)
  • Change to Full Screen Zoom
  • Resize Lens,
    Resizing the Zoom Window
    Resizing the Zoom Window

    which will allow you to resize the Zoom Window with the handles provided.

  • Choose Filter, which will set a colour filter on the Zoom Window
  • Show Controller
  • Change Magnification using the slider, which uses the setting from the Maximum Zoom Level, you set earlier.

Add a Magnifier to the Home Button

The new magnifier option isn’t for your screen, but rather for all the small print articles you need to read. From menus in dark restaurants to the label on a pill bottle, if you have your phone you can read it. Go to Settings > General > Accessibility  > Magnifier

turning on the magnifier
turning on the magnifier

Turn on the Magnifier and Auto-Brightness.

Now when you triple-click your Home button, an Accessibility Shortcuts menu will pop up. Tap the action you want to perform.

Choosing the Accessibility Shortcut
Choosing the Accessibility Shortcut