Thursday, 9 March 2017

Basic Excel

This page introduces some of the Basic Excel skills that will help when you are using functions and formulas in Excel.
Many useful Excel basics are described in the links below, but if you are a complete beginner, you might also find some useful tips on the Microsoft Office Basic Excel page.

Excel Data Input

Although you might consider it to be one of the most basic Excel tasks, there are actually several different ways of entering data into a spreadsheet. Many of these methods are shortcuts that will help you to save time and therefore work more efficiently.
Click on the links below for details.



Keyboard Shortcuts

It is worth spending a little time learning the Excel Keyboard Shortcuts for the actions that you perform most frequently in Excel. You will find that the time invested in learning these shortcuts will soon be repaid many times over.



Cell References

An understanding of absolute and relative cell references is essential when you are copying formulas between Excel cells. Click on the links below for details.



Excel Dates and Times

Did you know that Excel stores Dates & Times as numbers? An understanding of how this works will help you to work much more effectively when using Dates & Times in Excel. Click below for more details:



Converting Between Excel Data Types

In order to prevent problems, you may need to convert between the different Excel data types before using values in Excel functions and formulas.



Excel View Options

Excel allows you to change the view of your spreadsheet by zooming in or out, showing formulas, etc. Some of the more popular options are explained in the following links.



Excel Formatting

There is much more to Excel Formatting than just making spreadsheets look good.
In addition to making your spreadsheet look professional and smart, the formatting of data in Excel can also change the way the data is interpreted. The wrong formatting can sometimes result in data being read incorrectly and may give the impression that your function has failed to evaluate correctly. Click on the links below for more details.



Excel Subtotals

You can quickly and easily display subtotals in your data table using the built-in Excel subtotal command.



Excel Hyperlinks

The correct use of hyperlinks can turn a simple worksheet into a complete record-keeping system, which looks professional and is easy to use. Click on the links below for details.



Excel Filtering

One of the basic Excel requirements for anyone working with large amounts of data is the Excel filter. Excel has two main filtering types, which are described in the links below.



Pivot Tables

Learn how to use Excel Pivot Tables to quickly and easily analyse large amounts of data.



Password Protection

Sometimes you may want to protect your spreadsheet from being viewed or written to by others. The following pages describe how to password protect an entire workbook or individual sheets within an Excel file.



Saving To PDF

Click the link below for details of how to produce a PDF version of your Excel spreadsheet.

Excel Vlookup Tutorial

This Excel Vlookup Tutorial provides a step-by-step guide of how to use the Excel Vlookup function, providing examples and advice on fixing common Vlookup errors.
The Vlookup is one of the most popular functions in Excel but, until you fully understand it, it may initially appear complicated.
There are also some users have heard of the Vlookup function in Excel, but don't really understand what it does.
Therefore this tutorial begins by answering the common question, "What is Vlookup in Excel?" This is followed by an explanation of how to use the Vlookup function in the two situations when either an exact match, or a closest match, is required. The tutorial also provides practical Vlookup examples for each of these cases.
If you feel confident that you understand the Vlookup function, but your Vlookup is not working as expected, you may want to skip straight to the section on Vlookup errors. This section will take you through a logical set of steps that will help you to find the cause of your Vlookup error, and identify a solution.

What's new in Excel 2016 for Windows

Excel 2016 for Windows has all the functionality and features you're used to, with some added features and enhancements. If you purchased Excel 2016 without an Office 365 subscription, you get all of the features listed under "September 2015" at the bottom of the page. If you are an Office 365 subscriber, you get the features listed under "September 2015," plus all other updates as well. Keep in mind that updates first roll out to Office Insider participants, and later to consumer and commercial Office 365 subscribers. If you have an Office 365 subscription, make sure you have the latest version of Office.

Easier background removal
We've made it easier to remove and edit the background of a picture. Excel automatically detects the general background area; you no longer have to draw a rectangle around the foreground of your picture. The pencil for marking areas to keep or remove can now draw free-form lines, rather than being limited to straight lines.
Updates for Get & Transform and the Power Query add-in
  • Support for the same file extensions in Text and CSV Connectors:We have revised the list of supported file extensions in the From Text and From CSV connectors. Browse and select any text (*.txt), comma-separated value (*.csv), or formatted text space delimited (*.prn) file, as the first step of the import flow for both connectors. Switch to the “All files (*.*)” filter option to import data from any other unlisted file. 
  • ODBC and OLEDB Connectors – Support for “Select Related Tables”: We have enabled the Select Related Tables button in the Navigator dialog, when using the ODBC and OLEDB connectors. This option, which is already available for other relational data sources, allows you to easily select tables that are directly related to the set of already selected tables in the Navigator dialog. 
  • Enhanced Folder Connector – Support for “Combine” from the Data Preview dialog: In a previous update, we shipped a set of enhancements to the “Combine Binaries” experience. We've made further improvements to the feature this month. You can combine multiple files directly from the folder Data Preview dialog within the Get Data flow, without having to go into the Query Editor. Use the Combine or Combine & Load options to further refine your data before loading it into the Data model.
  • “Change Type Using Locale” option in Column Type drop-down menus inside Query Editor: In the Query Editor, you can view and modify column types using the Column Type drop-down menu in the preview area. With this release, we have added the Change Type Using Locale option to this drop-down (previously available when you right-click on the column header > Change Type > Using Locale). This option specifies the desired column type and locale to use for the conversion, and changes how text values are recognized and converted to other data types, such as Dates, Numbers, etc. 
  • “Insert Step After” option in Steps pane inside Query Editor: Use the new context menu option in the Query Editor window to easily insert new steps in existing queries. Insert a new custom step right after the currently selected step, which can be the final step or any previous step within the query. 

Wednesday, 8 March 2017

This juice detox by Jason Vale (7lbs in 7 days Super Juice Diet) is an ultra-quick method to reshape your body, while promising to provide you with all the nutrients your body needs. This juice plan promises quick weight loss to help you lose 7lb in only 7 days. (JM stands for juice master)

Super Juice

½ lime, peeled
2 apples, any type except Granny Smiths as they don’t juice well
½ pineapple
½ medium cucumber
½ ripe avocado
1oz fresh wheatgrass or wheatgrass powder
1 level tsp spirulina
1 capsule of acidophilus bacteria powder
Ice cubes
Juice the apples, pineapple, cucumber and lime. Put the avocado flesh into a blender with the ice, wheatgrass, spirulina and the bacteria powder.
Blend everything until smooth, pour and enjoy.

Special Juice

½ small pineapple
½ stick celery
1in chunk of cucumber
1 small handful of spinach leaves
1 small piece of peeled lime
2 apples
½ ripe avocado
Ice cubes
Juice the pineapple, celery, cucumber, spinach, lime and apples. Place the ripe avocado flesh in a blender along with the ice and juice mixture. Blend until smooth.
Pour into glass and enjoy.

BOOST JUICE

½ large pineapple
2 apples
½ mug Alfalfa sprouts
½ mug watercress
½ mug parsley
½ mug kale
½ mug broccoli
1oz shot of fresh wheatgrass juice (or 1 tsp wheatgrass powder)
2 ice cubes
Juice all the ingredients, then add the wheatgrass shot or teaspoon of wheatgrass powder. Pour into a glass over the ice.

SUPER DETOX JUICE

2 apples
½ cucumber
1 stick celery
1 small handful of spinach
Ice cubes
1 heaped tsp Dr Udo’s Beyond Greens supplement (if you don’t have this, replace by juicing a handful of mixed green leaves such as watercress, parsley or lettuce).
Juice the apples, cucumber, celery and spinach. Pour into blender, then add ice and Beyond Greens. Blend for about 1 minute or until smooth. If you use mixed green leaves instead of Beyond Greens, simply juice these along with the other ingredients and then add the ice.



Fruit Juices - Healthy Fresh Juices





Easy to prepare, a good friend of your taste buds, a hit all through the year and good for your health - that's a glass of fresh juice, which can also add glow and energy to your life. Fresh juice is healthy and we all know that drinking the real thing is better than the sugary alternatives that are packed months before they reach the consumers. Fresh juices benefit us in the following ways:
 
They provide minerals, vitamins, essential fatty acids, carbohydrates and proteins
Help improve energy levels
Good for the skin
Strengthen the immune system
Make the bones stronger
 
Despite all the benefits that fruit juices offer, there are certain things we need to know about fresh juice.
 
Is it okay to drink as much juice as we want daily?
 
No, water should be the main source of liquid and it is always better to have fruits in their natural form rather than as juice. Why? Because we all add sugar and salt to the fruit juice we make at home. This increases the amount of sugar in our diet and thus, the calorie load. But we don't really perceive drinks to be food. Eating a whole pomegranate or having three or four oranges in a row is a cumbersome job. But we would gallop a big glass of juice in a few seconds and even forget to count them as calories. So, if you take two servings of fruit in a juice, ideally you should forego your evening snacks.
 
Should we drink juice daily?
 
Drinking juice is an easy way to have two fruit servings per day that is good for the daily quota of vitamins. But, keep a check on the sugar added to the juice. It's best to have fresh juice without any sugar or salt. Though such juice might taste a bit bland and sometimes sour as in the case of orange juice, you would gradually develop a taste for them. The best choice for a daily habit of juice is carrot, apple and ginger, made with only one piece of fruit. Carrots are sweet but lower in calories and have about half the carbohydrates of fruits. Celery is also a nice low-calorie filler. Have a look at the amount of carbohydrates different fruits have.
 
Orange -16 g
 
Grapes - 28 g
 
Pomegranates - 26 g
Pineapple - 19 g
Apple - 21 g
 
How to use the juicer?
 
Now the next thing is how to use the juicer at home to get the best juice. The foremost thing is to get a good quality juicer, preferably ISI marked. If you want the fibre benefits, then put all the vegetables and fruits into a blender and don't use a juice extractor. While ordering fresh juice from outside, be sure about the hygiene and ensure that they are using a blender, not an extractor. Extractors make a smooth drink but they remove the all-important fibre from the juice.
 
Are juices good for children?
 
Replacing water with juice for children can cause diarrhoea. Having juice everyday can help to develop lifetime sweet tendencies in children. So, juices should be an occasional drink for children. Eating fruit in its natural form and relying on water, as the source of liquid, are better alternatives. Nevertheless, fruit juice is better than having aerated drinks.
 
Tips on getting best out of your juices
 
 Always wash fruit and vegetables - even organic produce could carry bacteria.
Include the stems and leaves of vegetables because they have a high vitamin and mineral content.
 
 Drink a juice within half an hour of preparing it, or it can get oxidised by exposure to air and develop a sour taste.
If you must prepare juice early, keep it refrigerated with a tight lid. Or freeze in daily-serving-size containers immediately and drink it straight after thawing.
 Include the white inner skin of citrus fruits, as it is full of beneficial bioflavonoids.
 Add lemon juice to preserve the natural colour and reduce the oxidation of essential nutrients.
If you want to lose weight, then drink vegetable juices, as they contain fewer calories than fruit juice.
If you have a sensitive stomach or irritable bowel syndrome (IBS), ensure that you dilute juices with water (three parts of juice with one part of water).
 
Easy to make juice-based drinks
 
Watermelon drink
 
Ingredients
3 cups watermelon (washed and cut into small pieces), sugar (as per taste), 3 tablespoon skimmed milk, ice cubes.
 
Method
Blend the ingredients in a blender and refrigerate. Pour the prepared drink into serving glass, add ice cubes and serve.
 
Nutritive value of the watermelon juice - Apart from providing energy, watermelon protects against age-related symptoms of vision loss, fights heart disease, reduces cancer risk and is loaded with anti-oxidants. So, a glass of watermelon drink everyday not only quenches your thirst but also offers a range of health benefits.
 
Carrot, celery and cabbage juice
 
Ingredients
2 cucumbers, stalks of celery, a piece of ginger, a handful of parsley, piece of apple or citrus fruit.
 
Method
Wash all vegetables and juice them together. Serve it fresh.
 
A glass of fruit or vegetable juice takes very little digestion. It goes right into your body and is a very yummy way of getting instant energy. Fruit juices can energise your life.

Juices May Reduce Alzheimer’s Disease Risk

(NewsTarget) In a large epidemiological study, researchers found that people who drank three or more servings of fruit and vegetable juices per week had a 76 percent lower risk of developing Alzheimer's disease than those who drank juice less than once per week.

The study by Qi Dai, M.D., Ph.D., assistant professor of Medicine, and colleagues appears in the September issue of The American Journal of Medicine. The researchers followed a subset of subjects from a large cross-cultural study of dementia, called the Ni-Hon-Sea Project, which investigated Alzheimer's disease and vascular dementia in older Japanese populations living in Japan, Hawaii and Seattle, Wash.

For the current study, called the Kame Project, the researchers identified 1,836 dementia-free subjects in the Seattle population and collected information on their dietary consumption of fruit and vegetable juices. They then assessed cognitive function every two years for up to 10 years.

After controlling for possible confounding factors like smoking, education, physical activity and fat intake, the researchers found that those who reported drinking juices three or more times per week were 76 percent less likely to develop signs of Alzheimer's disease than those who drank less than one serving per week.

The benefit appeared particularly enhanced in subjects who carry the genetic marker linked to late-onset Alzheimer's disease –- the most common form of the disease, which typically occurs after the age of 65.

The researchers chose to study this group because of the low incidence rate of Alzheimer's disease in the Japanese population. However, the incidence of Alzheimer's in Japanese people living in the United States is higher, approaching the incidence rates in Americans. This pointed to environmental factors like diet and lifestyle as important contributors to disease risk.

Originally, researchers suspected that high intakes of antioxidant vitamins (vitamins C, E and Beta-carotene) might provide some protection against Alzheimer's disease, but recent clinical studies have not supported this hypothesis.

"We thought that the underlying component may not be vitamins, that there was maybe something else," Dai said.

Dai began to suspect that another class of antioxidant chemicals, known as polyphenols, could play a role. Polyphenols are non-vitamin antioxidants common in the diet and particularly abundant in teas, juices and wines. Most polyphenols exist primarily in the skins and peels of fruits and vegetables. Recent studies have shown that polyphenols (like resveratrol in wine) extend maximum lifespan by 59 percent and delay age-dependent decay of cognitive performance in animal models.

"Also, animal studies and cell culture studies confirmed that some polyphenols from juices showed a stronger neuroprotective effect than antioxidant vitamins. So we are now looking at polyphenols," Dai said.

The next step, said Dai, is to test the subjects' blood samples to see if elevated levels of polyphenols are related to the reduced risk of cognitive decline and Alzheimer's disease. This would provide further evidence of the role of juice polyphenols in Alzheimer's disease risk. It also may point to the types of juice that would be most beneficial. "We don't know if it is a specific type of juice (that reduces risk). That information was not collected in the current study," said Dai. "But we can use plasma to narrow down the kinds of juices."

However promising the study results appear, Dai cautioned, it's important that the general public not jump the gun regarding the value of juice as a preventive measure for Alzheimer's disease.

"A few years ago, hormone replacement therapy, NSAIDs (nonsteroidal anti-inflammatory drugs) and antioxidant vitamins showed promise (in preventing or slowing Alzheimer's disease), but recent clinical trials indicate that they do not," Dai said. "More study, I think, is needed."

From my natural healing perspective I would recommend pomegranate, tart cherry, red grape juice and, of course, a glass of red wine. I would also think that juiced vegetables would be a good preventative against Alzheimer's disease.

Tuesday, 14 February 2017

Working with VB Namespaces

This article gives an overview on how to create flowcharts in Excel. There are significant differences between the tools in the newer versions of Excel and the older versions. Make sure that you read the appropriate section below. Most of the editing techniques are the same and are covered in the Editing Excel Flowcharts section at the bottom of the article. Most of the topics described here can also be applied to creating flowcharts in Word or PowerPoint, but in my humble opinion, of all the Office Drawing tools the Excel drawing tools are the most user friendly.


Setting Up The Environment

Before actually creating the flowchart, we will cover some preliminaries that make flowcharting in Excel a bit easier.
Creating a Grid (Optional)
A grid is not required, but it makes creating flowcharts with uniform shape sizes easier, especially when coupled with the Snap to Grid feature, which we will cover in the next section.
The grid is created by changing the column widths to match the standard row height. Assuming that you're using the default font of Calibri 11, the standard row height is 15 pts, which equals 20 pixels. To create the grid, change the column widths to 2.14 (= 20 pixels). (In case you're curious, the units for Excel column widths are based on the average number of characters that will fit within a cell.)
Enabling Snap
When Snap to Grid is turned on, anytime you add, move, or resize a shape, the edges of the shape will “snap” to the nearest grid line. Snap to Shape provides the same behavior, except shapes are snapped to the edges of other shapes. You can turn on both Snap to Grid and Snap to Shape by clicking the Page Layout tab, then click the Align dropdown, as shown in the image on the right.
Page Layout
Beyond the obvious reasons, setting the page layout before creating the flowchart is important for several reasons:
  • If you plan to copy the flowchart from Excel to Word, or some other application, matching the margins to the target is important. Word, for example, has different normal margins than Excel.
  • If the flowchart direction is left to right, the page layout is typically in landscape orientation.
  • When you display page breaks, they act as a visual boundary to check whether shapes fall within a page.
To set the layout, click the Page Layout tab and use the Margins, Orientation, and (paper) Size dropdowns to change the settings if needed.
Themes: Be careful changing the Theme on the Page Layout tab. It not only alters the font and color scheme, but it also changes the row heights and column widths, which will affect how many shapes fit on a page.

Creating the Flowchart

Inserting a Flowchart Shape
To add the first shape, starting by clicking the Insert tab, where you should see a Shapes dropdown button. Clicking the Shapes dropdown displays the gallery of shape types shown below.
You can add a shape to the worksheet either by double-clicking a shape in the gallery, or by single clicking a shape and drawing its outline on the worskheet while holding the left mouse button down. If you double-click to add the shape, the shape will be placed in a somewhat arbritrary location on the sheet and have a height of 0.67” and a width of 1.0”.
Adding More Flowchart Shapes
After you add the first shape, you'll notice that a Format tab (shown below) becomes available on the ribbon anytime that you click on a shape. We'll cover formatting in a bit, but in regard to adding shapes, the Format tab duplicates the Shapes gallery that we saw above. This makes it handy to add a shape then continue to add shapes in serial fashion.
Format Tab
Adding Text to a Shape
This is straightforward - just click on the shape and start typing. If you need to edit the text in the shape click in the center of the shape, and not on the edges. Clicking the text in the center will put you into edit mode, but clicking the shape's border will select the shape itself.
Adding Connector Arrows Between Shapes
Connector arrows are added to the worksheet the same way flowchart shapes are - via the Shapes gallery. After clicking the line type in the gallery, follow these steps to add it to the flow diagram:
  1. Hover the mouse over the first shape and you will see the available connection points highlighted by red dots. Click the left mouse button down on the desired connection point.
  2. While still holding the left mouse bbutton down, drag the line to the next shape, where again the connection points are highlight.
  3. Release the left mouse button on a connection point, and the line will be selected with both end points highlighted by red dots. If an endpoint has a clear dot, it indicates that the connector wasn't connected.
Adding Labels and Callouts
There are two common ways to add notes to a flowchart.
  • Text box labels: text box
  • Callouts: text box
Text boxes are often used to label the connectors coming out of decisions. Callouts are commonly used to add side comments, with their shape indicating that they are not a process step. Both can be added via the Insert Shapes gallery.

Formatting the Flowchart

There are so many formatting options in Excel, that it's too much to cover in a single article. The sections below show how to do basic shape and line formatting. There is a special dialog worth mentioning. If you right-click on a shape and select Format Shape from the context menu, the format dialog will display and let you make changes to a wide
Formatting Shapes
When one or more shapes is selected, the Format tab displays the style gallery shown below. Note that the styles changed in Excel 2013. The shape styles are set by their theme number, so if you use one of the purple styles in Excel 2010, for example, and then open it in Excel 2013 or Excel 2016, it will display with the new orange theme.
If you are sharing the document with Excel 2003 users, note that the last row of styles will not render well in Excel 2003. If you are sharing the file with Excel 2000 users (i.e., the file is saved in .xls compatibility mode), the last two rows of styles will not render well.