[Infographic] Excel Troubleshooting

In this infographic you will come to know how much percent of Excel Application is used by whom and in which fields. What is the use of Microsoft Excel files? But as mentioned above that Excel files corruption occurs due several reasons, so, I it gets corrupted and damaged then what they should do for Excel Recovery. (more…)

[Video] Repair Corrupt/Damaged Excel Files

Have you ever think what makes your excel file to get corrupt? May be it’s due to Improper way of transferring the Excel workbooks, Deleting the Excel workbooks mistakenly, hard drive crashing issue, malware malfunction your excel worksheet etc. Looking For Some Instant Solution To Recover Back Your Damaged Excel File? Then this video will surely going to help you in knowing about the best recommended solution to fix this issue.

How To Insert A Checkbox In Excel – Create An Interactive Checklist, To-Do List And Report

How To Insert A Checkbox In Excel

Inserting a checkboxes in excel sounds like a trivial thing but it opens up a host of new possibilities of your worksheets that will keep you on track  with your goals, schedule, assignments etc. Checkboxes and other controls like drop downs looks quite impressive in excel can be quite helpful while designing forms in excel.  These controlling options prevent users from entering some unwanted data in your forms and hence they are preferred to traditional textboxes.

This tutorial will guide you on how to make a checkbox in excel and use the check box results in formulas to create an interactive checklist, to do list, report or graph.

How To Insert Checkbox In Excel

Like other form controls, the check box control resides on the Developer tab, which doesn’t appear on the excel ribbon by default. So you need to turn it on first.

1. Show the Developer tab on the ribbon

In order to add the developer tab to the excel ribbon, do the following steps:

  • Right click on the ribbon and then tap to the Customize the Ribbon option… Or, click File > Options > Customize Ribbon.
  • Under the Customize The Ribbon option, select main tabs, check the Developer Box and click OK.

show-developer-tab-e1

Now with the developer tab in place, you will get access to a host of interactive controls, including Check Box.

2. Organize the data

To create excel checklist or to –do list, the very first step you need to do is to make a list of tasks or other items for which the check boxes will be inserted.

Suppose, you have created the following checklist for you party planning:

excel checklist data e2

3. Add a check box

After doing all preparations now you are getting the main part-add checkboxes to your party planning list.

To insert checkbox in excel, follow these steps:

  • On the developer tab, in the control group, click insert and select check box under form controls.

insert checkbox excel e3

  • Tap on the cell where you want to insert the first checkbox. The check box control will visible near that place, though not exactly positioned in the cell.

checkbox added excel e4

  • Properly position the check box, take your mouse pointer over it and as soon as the cursor changes to a four pointed arrow drag the checkbox where you want it.

position-checkbox-e5

  • Remove the text “Check Box 1“, right click the checkbox, select the text and delete it.

excel checkbox remove text e6

Your first excel checkbox is ready and only you need to copy it to other cells.

4. Copy the checkbox to other cells

Make selection of the cell with the check box by using the arrow keys on your keyboard and position the cursor over the lower right corner of the cell. When the mouse pointer changes to a thick black cross, pull it down to the last cell up to which you want to copy the checkbox.

copy checkbox excel e7

Now to all the items in checklist, you can see check boxes are added.

excel checkboxes added e8

As you can see excel checklist is almost ready. Now you ask why almost? Although the check boxes are inserted and you can’t check or uncheck them as excel is not able to respond to these changes because no cell is linked to any of the check boxes yet.

You let’s start knowing how to capture the user selecting or clearing the check boxes and how to use that information in the formulas.

ALSO READ:

How To Use Dynamic Data Labels To Create Interactive Excel Charts

9 Top Fixes & Solutions For “Excel Formulas Not Working Error”

How to link a checkbox to a cell

In order to capture the checkbox state (checked or unchecked) you need to associate the check box with a certain cell. To make this possible, follow these steps:

  • Right click the checkboxes, and then click Format Control.

format control e9

  • In the Format Control dialog box, switch to the Control/Tab, click in the cell link box and sheet an empty cell on the sheet to which you want to link to checkbox or type the cell reference manually:

link checkbox cell e10

  • Repeat the same process for other check boxes.

Tip:

To easily identify the linked cells, make selection in the adjacent columns that does not contain any other data. In this way you will be able to safely hide the linked cells later so they won’t clutter your worksheet.

  1. Finally, click on each of the linked checkboxes. In the linked cells, TRUE appears for selected checkboxes and FALSE for cleared checkboxes.

linked checkboxes excel e11

At this point, the link cells probably don’t make much sense, but please bear with me

Deleting a Checkbox

You can delete a check box by using these two ways.

First method:

  • Select a checkbox and press delete. This one is the fast method to do that.
  • And if you have more than one checkboxes in your worksheet :
  • Select all the checkboxes by holding control key.
  • And press delete to delete them all.

delete when you insert a checkbox in excel 12

Second method:

  • Use selection pane to delete them.
  • Go to home tab→ Editing → Find & Select → Selection Pane.
  • In selection pane, you will get the list of all the checkboxes you have used in your worksheet.
  • You can select each of them one by one or you can select more than one by one using control key. Once you select them, press delete.

insert checkbox in excel use selection pane in-excel e13

Wrap up:

Surely all these above mentioned steps will confidentially help you in customizing your excel sheet more enhancely by inserting checkboxes.

 

How To Use Dynamic Data Labels To Create Interactive Excel Charts

Use Dynamic Data Labels To Create Interactive Excel Charts

In excel worksheet Chart is a perfect tool to present data in an enhanced and more understandable way but most of the time it is seen that it sucks because of the overloading of excess of data representation. Actually the fact is that Chart looks more understanding when it is neat and clean. So the best way to keep your chart representation clean and understandable you need to make interactive charts in excel so that it don’t look too messy. As by doing this you can present more data in a single chart without worrying about all that clutter. (more…)

9 Top Fixes & Solutions For “Excel Formulas Not Working Error”

9 Top Fixes & Solutions For “Excel Formulas Not Working Error”

Undoubtedly excel provides numbers of function to analyze, audit and calculate data. Some of them are frequently used by excel users while some of them are specific that only small group of financial engineering or statistical specialist understand and need them.

Can anyone imagine using excel spreadsheet without using formula? I think no one and hardly anything could compare to the frustration caused when all of a sudden excel formula stop working and starts displaying “excel formula not working”.

Just as for instance suppose you have created the reports for your management meeting and just before print copies for the executives, you discover that the totals are all showing the last month values. How to you fix it? When such instances occurs the several questions starts running your mind that why is your excel formula not calculating? Why doesn’t this formula update its value by itself?  Don’t get panic most likely your excel worksheet is perfectly all right. Then what’s the issue to find out the reason read this tutorial as it explains the most common mistakes that happen when making formula in excel and how to fix a formula that is not calculating or updating automatically.

Symptoms:

Suddenly Excel formula not working correctly, it returns an error or some wrong result. Excel formulas not working  will arise problems like, formula returns an error or wrong result.

Solution To Fix Excel Formulas Not Working Error

1. Enter numbers without any formatting

While using the excel formula, don’t add any decimal separator or currency sign like $ or €. Remember in excel formulas , a simple comma is typically used to separate a function’s arguments and the dollar sign makes an absolute cell reference.

So instead of entering like this, $50,000 in your formula simply input the numeric value like this, 50000 and use the Format Cells dialog (Ctrl + 1) to format the output to your liking.

2. Make sure numbers are not formatted as text values

Another very common reason of excel formula not working is that number formatted as text values. It may looks like as a normal number but ms excel perceives them as text strings and don’t include it in the calculations.

check-the-cell-format-for-text

Visual indicators of text editor are as follows:

  • Numbers formatted as text are by default gets left –aligned while formal numbers are right aligned in cells.
  • The number format box on the home tab in the number group displays the Text
  • When many cells with text numbers are selected on the sheet, the Status Bar only shows Count; while usually it shows average, count and sum for numbers.
  • There may be green triangle shape in the top left corner of the cell or leading apostrophe visible in the formula bar.

make-sure-numbers-are-not-formatted-as-text-values

To fix this , just select all the problem having cells and click the warning sign and then select the Convert to Number option.

excel-convert-to-number

But in some cases, neither green triangles nor the warning signs appear in cells then in that case look at the number format box on the Home tab in the Number group. If it displays text, try cleaning all formatting for the problematic cells and set the cells format to number or general. If it won’t work then create a new column and manually input the data means copy your text to the notepad and then paste it on your excel sheet and delete the broken column.

3. Match all opening and closing parentheses in a formula

Generally arguments of excel functions are entered within the parentheses and in complex formulas; you may need to enter more than one set of parentheses. When creating such complex formula make sure to pair the parenthesis properly.

 Well excel display the parentheses pair in different colors as you enter them in formula. If your formula is missing some parentheses then excel will display the error and allow you to correct by balancing the pair.

 4. Enter all required arguments in an Excel function

Each excel function is having one or more required arguments. Some function are enclosed in [square brackets] in the formula’s syntax. So the formula must contain the required arguments, otherwise excel displays “You’ve entered too few arguments for this function” alert.

If the entered arguments are more than the required one than you will get “You’ve entered too many arguments for this function” error message.

5. Include the full path to a closed workbook

If you are using a formula that reference a closed excel workbook then your external reference must include the workbook name and entire path to the workbook. For example:

=SUM(‘D:\Reports\[Sales.xlsx]Jan’!B2:B10)

6. Do not nest more than 64 functions in a formula

If you are nesting two or more excel functions into each other for example suppose created nested IF formula, remember about the following limitations:

  • In excel 2016/2013/2010/2007 you can use max 64 nested functions.
  • While in Excel 2003 and lower, you can only use up to 7 nested functions.

7. Don’t enclose numbers in double quotes

In excel formula; the value encoded within the double quotes is interpreted as a text string.

That means, if you enter formula like=IF(A1>0, “1”), Excel will consider 1 as text and therefore you won’t be able to use the  returned 1’s in other calculations. So remove the double quotes and always write formula for numeric value don’t enclose number in double quotes unless you want them to be treated as text.

8. Enclose workbook and worksheet names in single quotes

When referring to other worksheets or workbooks that have spaces or non-alphabetical characters in their names, enclose the names in single quotation marks.

For example Reference to another sheet:
=SUM(‘Jan Sales’!B2:B10)

Reference to another workbook:
=SUM(‘[2015 Sales.xlsx]Jan sales’!B2:B10)

9. Separate function arguments with a proper character

Most of you must separate the function arguments with the comma, however this doesn’t work on all excel workbook. The character you use to separate arguments actually depends on the List Separator sets in your Regional Settings.

In European countries comma is used as decimal symbol and the list separator is usually set to semicolon; while in north America comma is default list separator.

For e.g: in North American you would write =IF(A1>0, “OK”, “Not OK”), while European Excel users  should put the same formula like this =IF(A1>0; “OK”; “Not OK”).

So if your excel formula are not working because of  “We found a problem with this formula…”error, then go the Regional Settings (Control Panel > Region and Language > Additional Settings) and check out what character is actually set here as List Separator there and then use exactly that character to separate arguments in your excel formulas.

Conclusion:

Hopefully, all these above mentioned tips will surely help you in solving off your excel formula not working issue.

How To Recover Missing Sheet Tabs In Microsoft Excel?

How To Recover Missing Sheet Tabs In Microsoft Excel?

You may have encountered with the situation where you open an excel workbook that has several worksheets, but only single worksheet appears to you.  If this happens your missing worksheet may get hide in plain sight due to some changes in excel setting. In order to restore the vanished worksheet tabs, here are few techniques which help you in easy navigation of workbooks easier, as well as other ways to find hidden worksheets. (more…)

How To Fix “Too Many Different Cell Formats” Error Message In Excel

Too Many Different Cell Formats excel error

Received “too many different cell formats” error messages in Excel, stop what you are doing. Take a look over your workbook and ask yourself “are all these cell format really useful? After you answer the inevitable smack yourself in the back of the head for creating such as mess! Just kidding, don’t actually smack yourself, but apologies to your co-workers for making them put up with your “creativity”. (more…)