Match a Single Value in Multiple columns (Excel Tricks)

Hii All,

After a long long time I am back. I always want to share some new things which may be useful for some or the others like me but due to busy schedule in my job I do not get time for the same.

Anyways, today My friend asked a query that he want to match a Single Value in Multiple Columns of the source data and if it matches anywhere in the those columns it will bring the related cell value 

The below pic will clear more about his query.

So it is bit tricky as normally you can use Excel Match formula in a single column, But with the Excel IfError Formula you can solve the issue.

The formula is 

=INDEX(M5:M9,IFERROR(MATCH(D5,J5:J9,0),IFERROR(MATCH(D5,K5:K9,0),MATCH(D5,L5:L9,0))))

You can also download the example file by clicking below link.

Matching Single Value in Multiple Columns

Hope this post is useful and you can corelate a situation where you needed something like this.

Thanks...
 

Tally Trial Balance Consolidation Utility in excel

Hello Friends,
Financial Year has ended recently and many of the accounts & finance professionals are ready for preparing the Balance Sheets.

Many companies have operations at multiple locations and they prepare the accounts accordingly and at the end consolidate the data and prepare the Final Accounts.

Normally the trial balances of each locations are consolidated and on that basis the Profit & Loss Accounts & Balance Sheets are prepared.

Sometimes, the consolidation of Trial Balance is lengthy, tricky and difficult part for the accounts professionals although its a simple process of summarizing the transactions.

So I am sharing an excel utility where upto 10 nos of Trial Balances (Tally Generated) can be consolidated within a minute.


The Excel utility is self explanatory, but some basic points are mentioned here: -
1. You need to export Group Names of each locations from tally and paste in this file.
2. You need to export Trial Balance of each location from tally and paste here.
3. You need to ensure that the Net Value should be Zero (0) .
    Because Debit = Credit, i.e. Debit-Credit = 0 (Otherwise there are some diff in the Trial Balance)


Hope this excel file be useful for your accounts work.


Click Here the download the Excel File

Multipurpose excel utility - Useful for every manufacturing /trading business with statutory compliances

Today sharing one of my excel programming which has helped me to the maximum in my professional career and allowed me to work with accuracy, speedy reporting & controlling. And this also allowed to access most data within a single excel file and not to make the computer messy with thousands of useless sheets.

I have named it as operation sheet.
The time taken to design this is around 10 Years. Yes the first operation sheet was designed in the Year 2007 which was just for Purchase & Sales data with no auto error checks and gradually I included many functions which are inter linked with the basic functions of a business (i.e. Purchase & Sales)

** This sheet has been designed considering the Place of business is within Odisha and so far no effects of GST has been included.


The Usefulness about this sheets (as per me): -
1. Purchases & Sales Databases (with all relevant transaction details which we are not able to maintain or generate through tally/sap) Like Waybill, Transportation details, auto freight calculation, delivery date & weight, debit / credit notes bill wise, Quality (QC) details of each transactions , etc.
2. Taxation: - It is helpful for cenvat credits of excise duty, VAT Input.
3. Statutory Forms : - You can track which C-forms are pending / received / issued / receivable, etc
4. Statutory Payments :  You can enter the statutory Payment details and it will automatically show in the Excise / VAT charts and further you can cross check the liabilities.
5. You can enter the Consumption details (party wise also) , Production, Change the valuation method  (FIFO/LIFO/WAVG) and track which stocks are in your stock yard.
6. You can also enter the bill wise receipts from customers and track the outstanding. (Although for more details reports I use another sheet)
7. You can track that which bills are shown in returns and which are pending (assume you receive bills late, missed due to some reasons etc)
8. Freight Payments / Deductions : -Auto calculate the freight payable and track with accounts.

Many More functions has been added whichever and whenever I felt the importance of any thing I have tried to add that one.


Download this Excel Utility

Download this Excel Utility

Sai Sampurna Padhi - A Sweet Bubbly Girl fighting for her life at Cuttack

Hello Everyone,
Today I want to share with you all about a girl named "Sai Sampurna Padhi"

Some details of this girl: -
Name                   : - Sai Sampurna Padhi
Age                       : - 2.5 Years
Residence           : - Bidanasi, Cuttack, Odisha
Father's Name    : - Linku Padhi
Mother's Name   : - Chandrika Mohapatra

Father's Occupation    : - Auto Driver at Badambadi Bus Stand, Cuttack


This girl is admitted to SCB Medical College because she is suffering from a very dangerous disease called "Blood Cancer" and as its at Stage-I doctors has prescribed to go for Bone Marrow transplant as soon as possible so that this can be cured. But the operation cost around 20-25 Lacks which they can't arrange.

For the time being, every week she has need of blood, plasma, test & medicine which is less as it is a government hospital but still they are unable to afford due to their financial condition.

The situation is such that the mental trauma is much bigger than we can think of. "We always used to say that money can't buy happiness, but its not true".  Money is bigger in today's world where most of the problems can be sorted out.

The mother cannot hold her tears and she feels herself so weak and alone as she can't do anything. His Grand Father who himself is handicapped is totally devastated to run here and there for her medicines, test reports and arranging funds for all these. Whatever savings and small loans they took is now already used. They have now appealed through Newspaper to all of us. CAN WE MAKE SOME DIFFERENCE. I think we can.

We all have tried to go for another option which is free of cost facility available for Low Income groups at "Tata Memorial, Navi Mumbai" but it seems its very difficult to get her admitted there because of long ques.


OUR APPEAL: 
I on behalf of my ACEAA team appeal to all of you to please look and understand the matter and try to help this girl child as much as possible.

Please also inform us if anyone can help her by getting her admitted to Tata Memorial.

If any other private / govt/ Trust hospital are providing the facility for free, then also please inform.u


You can contact us on : -
98532-37724 (Balaji Telikicherla of ACEAA)
72055-50128 (Grand Father of this child)

Or you can post your comments below or mail to sharma.ctc@gmail.com

You can also deposit whatever amount you like on the below accounts: -
SBI A/c. 10508885200 of Prabhat Kumar Mohapatra (Grand Father)
SBBJ A/c 62371026275 of Surykant Mohanty and Satyananda Routray (Trust Member of ACEAA)


To know more about ACEAA
Please visit on http://www.alumni-ace.org/
An institute of Commerce who is trying to not only educate with academics but also trying to educate humanity also to their students.

Such a cute lovely girl with amazing smile

Link of Newspaper where the information was shared.

http://www.sambadepaper.com/Details.aspx?id=343360&boxid=3829606 

 Link of Newspaper Website

My Friend and the Senior member of our association (ACEAA) who visits her daily at SCB Medical, 

UPDATED ON 14-APR-2017
ACEAA team has posted a banner and spread awareness at the Blood donation Camp organised by Eagle Club, Dewan Bazaar, Cuttack. Also a fund of around 10 K was collected and the same was handed over to the Grand father of this child to meet the daily medicine, food & lab-test expenses.

After the first round of chemotherapy at SCB Medical, she was discharged and went home, but she was again admitted after 2 days due to severe fever, cold and weakness. Her liver is also not functioning properly due to chemo. Her condition was not good and ACEAA team tried much to make her happy again. But we know she will smile happily only after getting fully cured. We also know that until she get admitted to Tata Memorial / AIIMS she will not cured properly.

Our team is trying hard and spreading the news and waiting for some help (from government / NGO)

We are also arranging funds for her routine expenditures and also support her family but its very difficult for us as so far a very less amount of fund has been collected.

Ending here today with a wish of some miracle.

~SERVICE TO MANKIND IS SERVICE TO GOD~

FAQs on Hydroponics

From last several weeks I was searching for this on web and now wanted to share it..

For those who do not know about "Hydroponics" , Let me tell you that this is an alternative to our conventional method of Farming. 

Hydroponics in simple words is "the process of growing plants in sand, gravel, or liquid, with added nutrients but without soil."

Why we need it because the urbanization is growing rapidly and the agriculture land along with manpower & labour is going down and rarely available. We have now to focus on some alternatives for our food.


Here are the FAQ List which will help.

What is Hydroponics?
Hydroponics is the method of growing plants using a soilless media which could include a wide variety of examples like: gravel, peat, vermiculite Perlite, old rubber tires, rockwool, and expanded clay aggregates. The minerals that the plant needs are dissolved into the water which is then watered directly to the plants. So, in short instead of the plants searching throughout the soil for their minerals they draw them directly from the water that they are being fed.

What is pH?
A measure of the acidity or alkalinity of a solution, numerically equal to 7 for neutral solutions, increasing with increasing alkalinity and decreasing with increasing acidity. We recommend that you keep your solution at about 6.5 because that is the point at which the nutrients are the most soluble.

What is PPM?
PPM is very easily defined as Parts Per Million and can be used as the measurement of a number of different things. More commonly in the hydroponics world, this measurement is used to measure the amount of Total Dissolved Solids in your nutrient solution or how much CO2 is in your atmosphere.

What is EC?
EC is a unit of measure to gauge the Electrical Conductivity of a solution. An EC meter applies an electrical voltage to the solution and reads the conductivity that is produced from the motion of mineral Ions.

What is the difference between High Pressure Sodium and Metal Halide?
The difference between these two types of HID (High Intensity Discharge) lights is the color spectrum that is emitted from each. The High Pressure Sodium bulb emits light that is concentrated in the red to yellow side of the spectrum and are weak in the blue-violet end. While the Metal Halide bulb emits light that is very balanced and contains all the energy peaks at wavelengths of the visible spectrum. Visually the Sodium bulbs will appear very yellow-orange and the Halide bulbs will appear more blue-white in color.

Should I use Sodium or Halide?
The Halide lights have a very balanced spectrum and are excellent for vegetative growth or leafy plants like lettuce and basil. The Halide lights produce between 65-115 lumens per watt which is a measure of the efficiency of the bulb, or how much light you are producing for the amount of electricity you are using. The Sodium lights produce light that is very bright and concentrated on the yellow to red side of the color spectrum. This color is not as balanced as the Halide but makes up for the lacking of a balanced spectrum in the amount of light given off by the bulb. The Sodium bulbs produce between 97 to 150 lumens per watt which is much higher than the Halide bulbs. The Sodium bulbs are superior in life expectancy and efficiency while the Halides a superior in spectral distribution so your decision will be based on what is more important to you.

Why do people use Metal Halides for vegetative growth and High Pressure Sodium's for flowering?
Many people switch between bulbs for different stages of growth for a couple of reasons. First of all, Sodium bulbs have been known to make some plants grow leggy and stretched out because of the yellow to red spectrum that they give off. Metal Halides tend to keep these plants tighter with less space between internodes. So some growers use the Metal Halide lights during vegetative growth to keep the structural growth of the plant nice and tight. But they switch to Sodium lights when the plants begin to flower because the Sodium lights produce so much more light than the Halides. Even though the Sodium's do not have as good a spectrum as the Halides the intense light that is put off by the Sodium's aids in flower development and fruit set. Do not be fooled though, you can use either light throughout the life of a plant and get excellent results.

What size light should I purchase?
The first thing you need to do is figure out what kind of square footage you are dealing with. Do not just figure for the whole room figure out what the plant area is that you need to cover and multiply length x width to get the square footage. Now, you will want to try and achieve at least 30 watts per square foot. So if you have a 4 x 4 area which is 16 square feet and you multiply by 30 watts, you get 480 watts. So for a 4 x 4 area you will need to use at least a 430 watt light. Remember though that the amount of light required will depend on the plants because some plants like lots of light and some like low levels of light.

Do I need a ballast?
Yes. A ballast is required to start the lamp and to increase the voltage required to run the lamp. The ballast is responsible for starting the lamps by providing a high, fast charge of electricity. After the bulb lights, the range of voltage and current are controlled by the transformer which is why the bulbs operate so efficiently.

Are HID lights efficient?
Yes, very efficient compared to standard incandescent bulbs found in your house. For example one 1000 watt sodium bulb produces as much light as about 87 standard 100 watt incandescent bulbs.

Can I interchange bulbs between systems?
No. You should never interchange bulbs between systems unless they are specifically made to do so. Lots of people ask if they can use a 250 watt bulb in a 400 watt system and the answer is definitely not. You could put yourself at risk by doing something like this because the bulb could become unstable and explode. You should also never put Halide bulbs in a High Pressure Sodium system because the ballast's are only meant to run the type of bulb they are rated for and a Halide bulb in a Sodium system could overheat and explode also. There are conversion bulbs manufactured that are High Pressure Sodium bulbs that are meant to run off of a Halide ballast. But once again only put the bulb in a system it is rated for.

Do these lights require any special wiring?
No. All systems manufactured by Greentrees are completely wired and just need to be plugged into any grounded wall outlet. Custom voltages can be built into ballast's per customer request (208v, 240v, 277v). Do not attempt to change the voltage of the ballast unless you are an experienced electrician. Connecting the wrong wires could result in a fried ballast or bulb, and even worse a fire.

What kind of plants can be grown using hydroponics?
Almost any type of plant can be grown using hydroponics some are just more delicate than others. Usually if you can produce ideal environmental conditions the hydroponic plants will be far superior to plants from the same stock grown in soil.

How often should I change my nutrient solution?
There really isn't an amount of time that should be used to determine how often you should change your solution. A good rule of thumb to follow first of all is to top your reservoir off with fresh water without any nutrient added. This is because you will lose water to evaporation and plant uptake but the strength of the solution does not necessarily drop with the level of the water. So, in some cases as the reservoir water level drops the solution concentration actually goes up or gets stronger. So, add only fresh water and then adjust your pH accordingly. Try and keep a record of how much water you are putting in the reservoir to top it off and once the amount of water added equals half of the reservoir capacity it is time to change the solution and rinse the reservoir and growing medium. For example if you have a 20 gallon reservoir and over the course of 12 days you have added 10 gallons of water, then it is time to change your solution.

Can I transfer plants from soil to hydroponics?
Yes, you can all you need to do is wash the roots of the plant by dipping in water and try to remove as much of the soil matter as possible. Be very careful with the delicate roots so the plant doesn't go into too much transplant shock. After the roots are somewhat clean go ahead and pot the plant in any of your favorite hydroponic media and begin a standard watering regimen with a touch of B-1 in the solution for a week or so to aid in the recovery from transplanting.

Do plants grown using hydroponics taste better than plants grown in soil?
Quite often this is the case because the plants grown utilizing hydroponics have all the essential nutrients readily available to the plant. In soil the important micro nutrients are often locked away in the soil where the plants cannot take full advantage of these minerals. That is why hydroponics is superior because the grower has complete control over what minerals the plants are feeding on and in what quantities. This advantage over soil often produces produce that is far superior in taste, color, size, and nutritional value.

What is the difference between Aeroponics and Aero-Hydroponics?
There is a very simple definition of the difference between these two methods of growing. Aeroponic systems have plants which roots dangle in the air and the roots are sprayed with nutrient water. In an Aero-Hydroponic system there are roots that dangle but half of the roots dangle directly into water and the other half are sprayed with nutrient water. The spraying action keeps the standing water moving and circulating at all times which works excellent to get oxygen infused water to the roots. This Aero-Hydroponic method has proven to work incredibly for rooting cuttings and all other stages of growth as well.

Why does an ebb and flow tray system have two fittings?
First of all the common names of those two fittings are a fill/drain fitting and an overflow fitting. The fill/drain fitting is a fitting that is attached to the pump and serves as the inlet for the water filling the tray and also serves as the outlet for the water to drain back into the reservoir. The overflow fitting consists of a number of different plumbing parts and acts as a watering level regulator. The concept is quite simple... you have a tray that is suspended somehow, or sitting on top of, a reservoir. Now the pump turns on and begins pumping water into the tray and the pump will stay on as long as the grower has determined the watering cycle should be (usually 30 min.). As the water level rises in the tray it reaches the top of the overflow fitting which then allows the water to spill back down into the reservoir. So you have now filled the tray with water and you are maintaining that watering level by means of the overflow fitting, which does not allow the water level to rise any higher. Now when the pump is turned off the water begins draining by means of gravity back down through the fill/drain fitting, and through the pump, into the reservoir. The height of the overflow is decided by the containers or growing media being used. You only want the water level to rise and submerge about 50% to 75% of the medium or containers. This is to keep the medium or containers from floating and/or tipping over.

How often do I need to water my plants in an ebb and flow system?
Watering will all depend on the type of plants being grown, the size of those plants, and what type of medium you are using. As you know plants are very particular about being over or under-watered so this is an important question. First you need to determine if the medium you are using is absorbent or repellant. If you are using Rockwool you are dealing with an absorbent medium while Hydrocorn is a good example of a repellent medium that doesn't hold a lot of water. You want your medium to be moist but not drenched and you want your medium to dry out somewhat between waterings. So, if you watch your plants before and after waterings it will be very easy to tell if you are watering too much or too little. If your plants wilt before you water, but perk up immediately after watering, you may want to water a little more often. If your plants wilt just after watering than you are watering too much, and you should allot a little more time in between waterings. A good general rule of thumb is to start plants being watered about 2 to 3 times a day and increase as plants show signs of needing water.

How long should I water the plants for in an ebb and flow system?
In an ebb and flow system you are flooding a tray or pot full of nutrient solution to feed the plant or plants in your system. You do not want to keep the roots flooded for too long or you will risk drowning delicate roots. A good rule of thumb for watering in an ebb flow system is to water just long enough to completely flood your tray or pot with nutrient solution and then let the system drain. Most readily available timers have 30 minute increments so you may have to flood the tray for a little longer than it takes to reach the overflow level. This is fine as long as you do not keep the roots completely submerged for longer than an hour. Roots like oxygen, and keeping them submerged for too long cuts off the oxygen supply and can have fatal effects on your plants. So just remember to water only long enough to fill the system completely, and don't keep the roots submerged for too long.

What CO2 system is right for me?
The deciding factor in this problem is almost always heat. CO2 generators burn either natural gas or propane to produce CO2. The combustion of these fuels produces CO2 as a byproduct. Now the downfall of the generators is the heat that is produced in the process. While the generators are on they can raise temperatures in a grow room considerably. The nice thing about generators is the availability of propane or natural gas and the price of gas is considerably less than CO2. On the other hand, CO2 regulators are hooked up to CO2 tanks and regulate the amount of gas being emitted through the use of a timer and a flowmeter. You set the Cubic Feet per Hour (CFH) on the regulator and open the solenoid long enough to charge the room with CO2 up to the desired PPM using the timer to control the intervals. CO2 regulator type systems do not produce any heat at all but are more expensive to maintain then the generator systems since the price of CO2 is much higher than propane or natural gas.

What is the difference between Miracle-Gro and other commercially available fertilizers and hydroponics nutrients?
Most of the fertilizers readily available are very general purpose mixes that do not work well for all types of plants. The nutrients that we sell are made specifically for plants grown in hydroponics systems that utilize sterile growing mediums. Most commercially available fertilizers are meant for fertilizing plants grown in soil and do not contain all the necessary trace elements. Our fertilizers contain all those trace elements, and are also made to be very soluble so that the plants can easily uptake the nutrients from the watering solution. Those other fertilizers will work to grow plants but they cannot compare to the results you will experience with our nutrients. Most commercially available fertilizers are like junk food for your plants giving you quick results that do not last very long.

What is the desired temperature range for the nutrient solution?
The optimal temperature of the nutrient solution should be in the range of 65 to 75 degrees Fahrenheit. Letting water stand uncovered in a container overnight will help dissipate any chlorine in the water before you add it to the reservoir.

How much electricity will I save running a grow light 240 volt?
There is no energy savings from running your lights at 240 volts. That is a misconception that many people have. The main advantage is the fact that you can run more lights on one electrical circuit. For example if you run a 20 amp 120 volt circuit, you can only run two 1000 watt lights on that circuit. If you were to wire the circuit up for 240 volts you can run four 1000 watt lights on that circuit. This makes for a lot less wiring but does not save you on your electricity bill because each light still uses the same amount of watts. More Info:Watts cheaper 110 or 220?

How can I tell if my seeds are viable?
This can be accomplished by presoaking your seeds. Fill a shot glass with distilled water and place your seeds in it. After 24 hours the viable seeds should have sunk to the bottom. Those still floating are most likely not viable and will not germinate.

How to Calculate Detention of Vehicles considering the complex Date & Time in excel

Some times it is difficult to calculate some cases which deals with Date & Time and calculating the Detention charges is one of them.


Please download the excel file (and do remember that it has been prepared on the basis the following basis.... which can be changed with the help of formulas)

https://drive.google.com/file/d/0Bw1qNoQ8YzHZY3E5VVl1SUduWm8/view?usp=sharing

List out the details of files in a specified folder - automatically through excel without vba.

Hiii.
I was preparing a list of files (govt. forms) but it was difficult to manually copy the file name and paste into the excel sheet as there are more than 300 files in that folder.

After 8-10 copy paste I thought to do it automatically and the time saved is now used for writing this blog. LOL

Well, You can actually automatically list out the names of files in a specified folder into excel, assume that in a school there are 1000 students and their admit card is in one folder.. and for some reasons you need to list out it into an excel sheet.. So how you will do it...........


Here is a simple and superb way to do that........

These are the steps assuming your folder name is "Admit Card" and it is stored in D Drive...
1. Open a new excel file
2. Click on "Formulas" and then "Define Naem"
3. In the name field type AdmitCardList   (You can put any other name also)
4. In refers to Column type: - =FILES("D:\Admit Card\*.*")&T(NOW()) and press OK.
5. Now In your sheet 1 and cell A1 type or copy paste this formula
 =IF(ISERROR(INDEX(AdmitCardList,ROW()-1)),"",INDEX(AdmitCardList,ROW()-1))

and drag it downwards and you will see that your list is automatically updating.....





Hope the above can save your time too in case of huge data working and you will smile remembering this post...

Byee............... If you require the excel file, please inform........

Salary, Investments, Taxable Income & Income tax calculator for the FY 2015-16 , i.e. AY 2016-17

Hii Friends,
Sharing an excel file prepared in a rush, but hope it will ease your income tax & salary calculation...

Please post your comments and inform for any errors found as it has not yet been tested for errors.


Download the excel utility from here  https://drive.google.com/file/d/0Bw1qNoQ8YzHZbEpjMDJXVXZ6VTA/view?usp=sharing

You can do if you want......

Dear Friends,
Very interesting:

One day all the employees reached the office and saw a big advice written on the door
Yesterday the person who has been stopping your growth in this company passed away. You are invited to join the funeral.

In the beginning, they got sad for the death of one of their colleagues, but after a while they got curious to know who was the man who stopped their growth.

Everyone thought: Well atleast the man who stopped my progress died!

One by one the thrilled employees got closer to the coffin, and when they looked inside they were speechless. They stood shocked in silence, as if someone had touched the deepest part of their soul. There was a mirror inside the coffin and everyone who looked inside could see himself. 

There was a sign next to the mirror that read:
There is only one person who is capable to set limits to your growth...It is you. You are the only person who can influence your happiness, success and realization.

Your life does not change when your boss friends or company change.....your life changes when you change...you go beyond your limiting beliefs and you realize you are the only one responsible for your life.
Its the way you face life that makes the difference!

If an egg is broken from outside force....life ends but if it is broken from inside force life begins. Great things always begin from our inside.
Worth reading....

How cruel are we human beings ........ and how sweet are these animals....... Please watch......

We call ourselves as civilized, well mannered, well behaved human beings and think that we are the best creature of God... But are we ????????????/ ,, Just think once again after seeing this video.......

Some Useful tips on MS Excel - Specially for those who are using Excel

Quick Help

To get quick help on any menu item push Shift+F1 and click the menu item

Insert Today's Date

To insert Today's date push Ctrl+; (semicolon)

Insert Current Time

To insert the current time push Ctrl+Shift+: (Colon)

Show the Paste Function (Function Wizard)

Push Ctrl+F3

Show the GoTo dialog

Push F5

Show the Paste Names dialog

Push F3. This will only work if you have named ranges.

Name a Range

To name a selected range, click in the "Name box" (far left on the formula bar) and type a one word name.

Go To a Named Range

To go to a named range select it from the "Name box" (far left of the formula bar). Or push F5.

Edit a Named Range

To delete or edit a named range go to Insert>Name>Define or Push Ctrl+F3.

Headings as Range Names

Highlight your range including the headings and go to Insert>Name>Create or push Ctrl+Shift+F3.

Named Formula

To make a Name refer to a constant formula e.g. "TaxRate", go to Insert>Name>Define and type TaxRate in the "Names in Workbook" box and 36% in the "Refers To". Now enter =(10*TaxRate) anywhere on the Worksheet.

Named Range List

To obtain a list of all Named Ranges and where they refer, select any blank cell (make sure you have no data underneath or 1 column over) and go to Insert>Name>Paste then Paste List.

Nested Formulas

To help write nested formulas (more than 1 formula in a single cell) use the "Paste Function" i.e. Insert>Function or Shift+F3. Select the function that you need, enter the reference, number or text then select the drop arrow to the left of the formula bar to add more Formulas. Doing it this way ensures all your parentheses are in the correct places.

Debugging Formulas

To troubleshoot complex formulas select the cell containing it and then click the = (Equal sign) to the left of the formula bar, this will activate the "Paste Function". To step through your formula simply click in the part of the formula you want to debug.

Personal Help

To add your own text to any of the Office Assistants help files, push F1, enter your question then open the file. Go to Options>Annotate and type in your own text then click OK. You will now notice a paperclip symbol next to the heading, this will let you know that you have added your own Help in a way you will understand.

Different Help

Sometimes the Office Assistant is not very helpful to your needs, so try the "Context and Index" help by either clicking Help>Context and Index or selecting "Help Topics" from any "Help" file.

Customizing Toolbars

Right click on any Toolbar and select "Customize" or push Ctrl+Shift+F10 twice then "Customize". Now click the "Commands Tab" and drag menu items both on and off the Toolbars. If things get a bit messy simply click the "Toolbars" tab and click "Reset". This will return all menu items to their default.

Quick Charts

To create quick charts, click anywhere within your data and push F11.

Worksheet Template

Set up your Worksheet how you want it e.g. formatting, formulas etc then delete all other sheets in the Workbook. Now go to File>Save or Alt+F2 and select "Template (*.xlt)" from the "Save as Type". Type a name and click "Save" Now right click on the sheet tab and select Insert you should see yourTemplate sheet.

Secret Menu

Click in any cell, then move your mouse pointer over any border of the cell until the mouse pointer changes to an arrow, right click and drag to it's destination and then release.

Secret Menu 2

Place a date in any cell, then move your mouse pointer over the bottom right corner of the cell (Fill handle) until the mouse pointer changes to a small black cross. Now right click and drag to any cell and release.

Quick Cell Move

Click in the cell(s) then move your mouse pointer over any border until the mouse pointer changes to an arrow, left click and drag to it's destination and then release.

Quick Cell Copy

Click in the cell(s) then move your mouse pointer over any border until the mouse pointer changes to an arrow, left click and hold down the Ctrl key and drag to it's destination and then release.

Change Formulas to Values

Click in the cell(s) with the formula(s) then move your mouse pointer over any border until the mouse pointer changes to an arrow, right click and drag to the next cell, now still holding down the right mouse button drag back to where you started and release. Now select Copy here as values only.

Quick List

To quickly copy down the contents of a cell that has a list in the column to the left or right of it, simply click in the cell you want to copy and then Double click the Fill handle (little black square on the bottom right of the cell).

Fill Blank Cells Within a List

Let's say you have a list of entries in column A and within the list you have many blank cells. Here is a quick way to fill those blanks with the value of the cell above. Highlight column A, then push Ctrl+G and click Special then check the Blanks option and click OK. Now push Equals (=) then the Up arrow and finally holding down the Ctrl key push Enter.

Auto Fill

To fill a series across columns or down rows type January or Jan in any cell and place your mouse pointer over the bottom right corner of the cell (Fill handle) until the mouser pointer changes to a small black cross. Left click and drag down or across. This can also be done with Numbers, Weekdays, Quarters or any text that ends in a number e.g. Day1.

Custom Auto Fill

Type your list across columns or down rows. Now go to Tools>Option and select the "Custom Lists" tab. Click the collapse dialog box to the right of the "Import list from cells" box, highlight your range, click the expand dialog and then click "Import". Or type your entries in the "list Entries" box.

Adding Text to Formulas

To show a formula result and text or number(s) in the same cell type a & (Ampersand) after the formula then your text/number(s).

Adding Hidden Text to Formulas

Imagine you have a formula like: =$2018+$1056-4*$120. When you initially wrote it you knew what each number represented, but you come back later and can't remember. Add a hidden note to your formula by using the N() formula i.e. =$2018+$1056-4*$120+N("My Wage+Bonus-4 weekly loan repayments"). The N() function will convert text to zero.

Custom Format

You can format a cell to show any number or text without changing it's real value using "Custom Format". To see this type the number 20 in any cell then go to Format>Cells or push Ctrl+1. Select the "Number" tab and then select "Custom." Using any one of the pre-defined formats type "Twenty" (without quotations) or any text and then click "OK". To test it use the cell in any formula.

No More Chart Gaps

If you have a chart that is plotting empty text ("") or 0 (zero) from a formula then instead of using "" or 0 if the formula is False try using "#N/A" (without the quotations) or the formula =NA(). Or you can hide the Row(s) or Column(s). Either way Excel won't plot #N/A or hidden Rows or Columns.

My List

If you have a long list of Text with no blank cells between and you want to see a preview of what is in your list. Click in any cell within your list then right click and select "Pick from list", If you select one of the entries, Excel will insert it in the cell for you.

Remove Blank Rows

Highlight your range and go to Edit>Go to>Special and select "Blanks" then "Ok" now go toEdit>Delete or Ctrl+Shift+= (equal) and then select "Entire row" from the "Delete" dialog and click "Ok".

Sort Out Blank Rows

The quickest way to remove all blank rows is to select you range then go to Data>Sort.

See Formula cells

If you have a sheet full of formulas and you want to identify these cells at a glance go to Edit>Go to>Special and select "Formulas" then click "OK". Now go to Format>Cells or Ctrl+1 and select the "Patterns" tab and choose a color.

En Masse Changes

To make changes to more than one worksheet at the same time select one of the sheets, hold down your Ctrl key and click on each sheet name tab. Now any data entered one sheet will also be entered on the other(s). When you have finished right click on any of the sheet name tabs and select "Ungroup sheets".

En Masse Changes 2

Another way to have changes on one worksheet reflected on other sheets is to make all the changes you want on one sheet then hold down your Ctrl key and select the other sheet tabs. Go to Edit>Fill>Across Worksheets and Excel will give you 3 choices of what to copy to the other sheets i.e. "All", "Contents" or "Formats".

Worksheet Copy

Select the sheet name tab then hold down your Ctrl key and simply drag it to the position you want it.

Paste Reference

An easy way to reference another cell is to select the cell you wish to reference then right click and   select Copy or Ctrl+C then select the cell you want the reference in, right click again and select "Paste Special" then click "Paste Link"

Absolute/Relative Toggle

If you have a formula you want to make absolute or relative then double click in the cell or F2 then place the insertion point anywhere in the cell address and push F4 1, 2 or 3 times.

Repeat

To repeat an operation push F4

Undo

To undo an operation push Ctrl+Z

Linked Picture

A good alternative to a textbox or any shape is a linked picture that reflects any changes made to its reference. To make one, copy your cell(s), select the destination cell and holding down your Shift key go to Edit<Paste Picture Link.

Run a Macro by Clicking a Cell

This is possible with use of VBA but let's face it most people don't know VBA so here is an easy way. Select the cell you want to run the macro and hold down your Shift key and go to Edit>Copy Picture then select "As shown on screen" from the "Copy Picture" dialog then hold down your Shiftkey again and go Edit and click "Paste Picture". Now right click on the cell picture and "Assign Macro".

Non Formula Result

Sometimes you just want the result from the Sum, Average, Min, Max etc from a group of cells without typing a formula in a cell. Excel allows you to do this very easily, first highlight the cells you want to evaluate then right click on the "Status Bar" and select the function you want and your result will be displayed in the "Status Bar".

Reduce File Size

When you have a workbook that is very large in size you can reduce this dramatically by saving the file as "Microsoft Excel Workbook (*.xls)" as apposed to "Microsoft Excel 5/95 Workbook (*.xls)". In other words avoid saving as multiple versions whenever possible. Also click here for much more details and other methods.

Cell Navigation

To move through a group of cells that you are working with without going outside the range highlight the group of cells and then use the "Enter" key to move through them.

Quick Formula Syntax

When writing formulas for Excel sometimes you just need a quick reminder of the formula syntax. In this is the case then type an equal sign followed by the function name and push Ctrl+Shift+A. For Example typing =Vlookup and then pushing Ctrl+Shift+A will give you: =vlookup(lookup_value,table_array,col_index_num,range_lookup). The non-bolded arguments are optional.

How to copy formulas without the reference changing

This can be achieved by either pressing F2 and then highlighting the formula, Copy, Enter then paste to destination. Or doing the same in the Formular bar. However, this is not much good for large amounts of data, so try this: Select the range of cells with Formulae, use the Ctrl key for non-contiguous ranges. Now go to Edit>Replace and Replace = with #. Copy and paste to your location and then simply use Edit>Replace # with =

How to copy and transpose formulas without the reference changing

In cell A1 of sheet 2 put: =Sheet1!A1 now copy this down a max of 255 rows. Now with the formulas selected go to Edit>Replace and Replace = with #. Now copy, select cell B1, go to Edit>Paste special and choose Transpose. Delete Column "A" and with Row 1 selected go to Edit>Replace and Replace # with =

Turn a List Upside-Down

1. Copy the list to another location using Copy, Edit>Paste Special>Value.

2. Now select all data in the list, go to Tools>Options>Custom Lists.

3. Ensure the list address is in the "Import list from cells:" and click "Import".

4. Now go back to the column next to your list and in the top cell place the LAST entry from your list.

5. Now in the cell below, place the second last entry.

6. Select both cells and double click on the Fill Handle (small black square bottom right).

The list should now be reversed. You could now also sort you original list using Data>Sort>Options, nominate your list then sort!

Formula Errors

Whenever typing one of Excels functions (especially nested ones) into a cell always use lower case. This way when you push Enter Excel will capitalize only the names of the functions you have entered correctly.

Entering Named Ranges Into Formulas

When you write a formula, sometimes you want to use a Named Range as one of the arguments for the formula, but you cannot remember the name. In these times simply push F3 when you reach the argument that you want the Named Range in and Excel will display the Paste Name dialog. Click the name you want then OK.

Optional Function Arguments

Sometimes you may not be sure what arguments in a function are optional and which are not. If your using the Paste Function (Function Wizard) then the non-bolded arguments are optional.

Sort by more than 3 Columns

Excels sort feature only allows to nominate up to 3 columns to sort by, here is how to get around this. The key to this is sorting by the last key first and working back to the first key. Say you data is in Column A:E and you want to sort by A, B, C , D then E

1. Select all of Columns A:E

2. Go to Data>Sort> sort by C then by D then by E

3. Click Sort

4. Now again with Columns A:E selected

5. Go to Data>Sort> sort by A then by B

6. Click Sort

Printing Workbooks

If you have quite a few Workbooks to print, go to File>Open from within Excel, select the Workbook(s) uisng the Ctrl key, then right click and choose Print. For any confusion or clarification, please feel free to comment.