The formula method is pretty straight forward, with one minor issue: You specified NO for less than zero and YES for greater than zero, but it is not clear what you want as a result when E12 equals zero.
This formula should meet your requirements and return a blank cell if E12 = 0
In Excel Online, you can view a histogram (a column chart that shows frequency data), but you can’t create it because it requires the Analysis ToolPak, an Excel add-in that isn’t supported in Excel for the web. If you have the Excel desktop application, you can use the Edit in Excel button to open Excel. The IF function in Excel returns one value if a condition is true and another value if it's false. You can use up to 64 additional IF functions inside an IF function. Excel has other functions that can be used to.
=IF(E12<0,'NO',IF(E12>0,'YES','))
These formulas will return NO only when E12 is less than zero.
=IF(E12<0,'NO','YES')
=IF(E12>0,'YES','NO')
=IF(E12>0,'YES','NO')
Basing the result in F12 on the color of E12 is a bit more difficult. There are 2 ways (that I know of) to have a specific value (string or number) appear in a cell based on the format of another cell.
1 - Use a macro. The macro could monitor the fill color (or condition that set the fill color) of E12 and then put YES or NO in F12 based on what it sees.
2 - Use Conditional Formatting to hide part of a string by using 2 rules. Here's what I mean:
First, put Yes No in F12 and manually change the font color of each word so that it looks like this:
YESNO
Then use these 2 Conditional Formatting rules on F12:
=E12<0 (Fill with Red)
=E12>0 (Fill with Green)
=E12>0 (Fill with Green)
Based on the value in E12, the Conditional Formatting fill color of F12 will hide either the Green word (NO) or the Red word (YES).
How To Post Data or Code ---> Click Here Before Posting Data or VBA Code
If you’ve worked with Excel long enough, you’ve probably needed to get data into Excel so that you can analyze it, or maybe create a chart. Where does this data come from? It might come from a database or some other program, or even the web. And it can come in a variety of forms.
Whatever the case, I can guarantee you the last thing you’d ever want to do is retype it, so that option is out unless you really love typing and have tons of extra time on your hands. And copying from another program or from the web and pasting directly into a cell in a workbook might leave you with nightmarish looking results I’ve seen this, believe me.
Luckily for you, there’s a much better way to get that data into Excel. Importing! Yes, importing is not just for purveyors of fine art or exotic foods.
When you use the Text Import Wizard, you don’t open your data source in its native program. You point Excel’s Text Import Wizard to a file, and the wizard leads you through a few steps where it looks at the rows of data in the source file and makes suggestions based on the layout of the file. Of course, you’re allowed to fine tune or override these suggestions so that the data comes into Excel just the way you want.
In this file, the four pieces of information for each employee are separated (also known as “delimited”) by a comma. The first row of the file is the column names, which are also separated by commas.
The rows of information look just like this in the text file:
Start the Text Import Wizard
To import the contents of the file, select an empty cell in Excel — this can be in a new workbook, a new worksheet in an existing workbook, or in an existing worksheet. The wizard isn’t picky!
Now, click the Data tab, and in the Get External Data group, click From Text.
This starts the Text Import Wizard. Go find your file, and click Open. Notice how the Delimited button is already selected?
If the file is a text file (.txt), Excel starts the Import Text Wizard. When you are done with the steps, click Finish to complete the import operation. See Text Import Wizard for more information about delimiters and advanced options. If the file is a.csv file, Excel automatically opens the text file and displays the data in a new workbook. In the Customize the Quick Access Toolbar window, click Import/Export, then click Export all customizations, and save the customizations file to some folder. Import a customized QAT: In the Customize the Quick Access Toolbar window, click Import/Export, select Import customization file, and browse for the customizations file that you saved earlier. In the Text Import Wizard, you can now scroll to the right to see all the columns. Fixed an issue with freeze panes displaying incorrectly after zooming. Fixed several issues with VBA, including Application.OperatingSystem now returns the correct value. Numerous other bug fixes. From Excel’s “Data” tab, click the “Text to Columns” button found in the “Data Tools” section. This will bring up the “Convert Text to Columns Wizard” window and allows you to begin separating your data. From the options, select the “Delimited” radio button and click “Next” to continue. By default, Excel will choose to.
My data certainly is delimited by those commas, so I’ll click Next. The default delimiting character in Excel is the tab character, so it’s pre-selected here in step 2. Notice how, with the tab character as the delimiter, the preview doesn’t look right? So I see I need to change that from Tab to Comma.
Now I’ve selected Comma instead. There, see how the preview changes and looks right now?
I’ll click Next to go to the third and last step of the wizard. I can select each column and, if I want, change its data type so that it has a format I prefer or a right or left margin. But all of these columns look fine, so I’ll click Finish.
Now, Excel asks me if I want to import the data right here in the current cell or create a new worksheet. That’s nice, because what if I had selected a cell that contained data I didn’t want to overwrite? Anyway, I’m in blank cell in a new worksheet, so I’ll accept Existing Worksheet and cell =$A$1, and click OK.
And here’s my imported data! As a plus, the Text Import Wizard (in most cases) optimizes the width of the columns to fit the data so you don’t have to.
— Gary Willoughby
In this tutorial, we will have an in-depth look at how to use and customize Quick Access Toolbar in Excel 2010, Excel 2013, Excel 2016 and Excel 2019.
Getting to the commands you use most often should be easy. And it is exactly what the Quick Access Toolbar is designed for. Add your favorite commands to the QAT so they are only a click away no matter what ribbon tab you currently have open.
- How to customize Quick Access Toolbar
What is Quick Access Toolbar?
The Quick Access Toolbar (QAT) is a small customizable toolbar at the top of the Office application window that contains a set of frequently used commands. These commands can be accessed from almost any part of the application, independent of the ribbon tab that is currently opened.
The Quick Access Toolbar has a drop-down menu containing a predefined set of the default commands, which may be displayed or hidden. Additionally, it includes an option to add your own commands.
There is no limit to a maximum number of commands on the QAT, although not all the commands may be visible depending on the size of your screen.
Where is Quick Access Toolbar in Excel?
By default, the Quick Access Toolbar is located in the upper left corner of the Excel window, above the ribbon. If you want QAT to be closer to the worksheet area, you can move it below the ribbon.
How to customize Quick Access Toolbar in Excel
By default, the Excel Quick Access Toolbar contains only 3 buttons: Save, Undo and Redo. If there are a few other commands that you use frequently, you can add them to the Quick Access Toolbar too.
Below, we will show you how to customize the Quick Access Toolbar in Excel, but the instructions are the same for other Office applications such as Outlook, Word, PowerPoint, etc.
Quick Access Toolbar: what can and what cannot be changed
Microsoft provides many customization options for the QAT, but still there are certain things that cannot be done.
What can be customized
You are free to personalize the Quick Access Toolbar with things like:
- Add your own commands
- Change the order of commands, both default and custom.
- Display the QAT in one of the two possible locations.
- Add macros to the Quick Access Toolbar.
- Export and import your customizations.
What cannot be customized
Here's a list of things that cannot be changed:
- You can only add commands to the Quick Access Toolbar. Individual list items (e.g. spacing values) and individual styles cannot be added. However, you can add the whole list or entire style gallery.
- Only command icons can be displayed, not text labels.
- You cannot resize the Quick Access Toolbar buttons. The only way to change the buttons size is to change your screen resolution.
- The Quick Access Toolbar cannot be displayed on multiple lines. If you've added more commands than space available, some commands won't be visible. To view them, click the More controls button.
3 ways to get to the Customize Quick Access Toolbar window
Most customizations to the QAT are done in the Customize Quick Access Toolbar window, which is part of the Excel Options dialog box. You can open this window in one of the following ways:
- Click File > Options > Quick Access Toolbar.
- Right-click anywhere on the ribbon and select Customize Quick Access Toolbar… from the context menu.
- Click the Customize the Quick Access Toolbar button (the down arrow at the far-right of the QAT) and choose More Commands in the pop-up menu.
Whatever way you go, the Customize Quick Access Toolbar dialog window will open, where you can add, remove, and reorder the QAT commands. Below, you will find the detailed steps to do all the customizations. The guidelines are the same for all versions of Excel 2019, Excel 2016, Excel 2013 and Excel 2010.
How to add a command button to Quick Access Toolbar
Depending on what kind of command you'd like to add, this can be done in 3 different ways.
Enable a command from the predefined list
To enable a currently hidden command from the predefined list, this is what you need to do:
- Click the Customize Quick Access Toolbar button (the down arrow).
- In the list of the displayed commands, click the one you wish to enable. Done!
For example, to be able to create a new worksheet with a mouse click, select the New command in the list, and the corresponding button will immediately appear in the Quick Access Toolbar:
Add a ribbon button to Quick Access Toolbar
The fastest way to add to the QAT a command that appears on the ribbon is this:
- Right-click the desired command on the ribbon.
- Select the Add to Quick Access Toolbar in the context menu.
That's it!
Add a command that isn't on the ribbon to Quick Access Toolbar
To add a button that is not available on the ribbon, carry out these steps:
Microsoft Excel For Mac Text Import Wizard Can 27t See All Columns Excel
- Right-click the ribbon and click Customize Quick Access Toolbar… .
- In the Choose commands from drop-down list on the left, select Commands Not in the Ribbon.
- In the list of commands on the left, click the command you want to add.
- Click the Add button.
- Click OK to save the changes.
For example, to have the ability to close all open Excel windows with a single mouse click, you can add the Close All button to the Quick Access Toolbar.
How to remove a command from Quick Access Toolbar
To remove either a default or custom command from the Quick Access Toolbar, right-click it and pick Remove from Quick Access Toolbar from the pop-up menu:
Or select the command in the Customize the Quick Access Toolbar window, and then click the Remove button.
Rearrange commands on Quick Access Toolbar
To change the order of the QAT commands, do the following:
Microsoft Excel For Mac Text Import Wizard Can 27t See All Columns Instead
- Open the Customize the Quick Access Toolbar window.
- Under Customize Quick Access Toolbar on the right, select the command that you want to move, and click the Move Up or Move Down arrow.
For example, to move the New File button to the far-right end of the QAT, select it and click the Move Down arrow.
Group commands on Quick Access Toolbar
If your QAT contains quite a lot of commands, you may want to sub-divide them into logical groups, for instance, separating the default and custom commands.
Though the Quick Access Toolbar does not allow creating groups like on the Excel ribbon, you can group commands by adding a separator. Here's how:
- Open the Customize the Quick Access Toolbar dialog window.
- In the Choose commands from drop-down list on the left, pick Popular Commands.
- In the list of commands on the left, select <Separator> and click Add.
- Click the MoveUp or MoveDown arrow to position the separator where needed.
- Click OK to save the changes.
As the result, the QAT appears to have two sections:
Add macros to Quick Access Toolbar in Excel
To have your favorite macros at your fingertips, you can add them to the QAT too. To have it done, please follow these steps:
Excel For Mac 2018 If Then Strings
- Open the Customize the Quick Access Toolbar window.
- In the Choose commands from drop-down list on the left, select Macros.
- In the list of macros, select the one you wish to add to the Quick Access Toolbar.
- Click the Add button.
- Click OK to save the changes and close the dialog box.
As an example, we are adding a custom macro that unhides all sheets in the current workbook:
Microsoft Excel For Mac Text Import Wizard Can 27t See All Columns Rows
Optionally, you can put a separator before the macro like shown in the screenshot below:
Customize Quick Access Toolbar for the current workbook only
Microsoft Excel For Mac Text Import Wizard Can 27t See All Columns Attached
By default, the Quick Access Toolbar in Excel is customized for all workbooks.
Excel For Mac 2018 If Then String Button
If you'd like to make certain customizations for the active workbook only, select the current saved workbook from the Customize Quick Access Toolbar drop-down list, and then add the commands you want.
Please note that the customizations made for the current workbook do not replace the existing QAT commands but are added to them.
For example, the Conditional Formatting button that we have added for the current workbook appears after all other commands on the Quick Access Toolbar:
How to move Quick Access Toolbar below or above the ribbon
The default location of the Quick Access Toolbar is at the top of the Excel window, above the ribbon. If you find it more convenient to have the QAT below the ribbon, here's how you can move it:
- Click the Customize Quick Access Toolbar button.
- In the pop-up list of options, select Show Below the Ribbon.
To get the QAT back to the default location, click the Customize Quick Access Toolbar button again, and then click Show Above the Ribbon.
Reset Quick Access Toolbar to the default settings
If you wish to discard all your customizations and revert the QAT back to its original setup, you can reset it in this way:
- Open the Customize the Quick Access Toolbar window.
- Click the Reset button, and then click Reset only Quick Access Toolbar.
Export and import a custom Quick Access Toolbar
Microsoft Excel allows saving your Quick Access Toolbar and ribbon customizations into a file that can be imported later. This can help you keep your Excel interface looking the same on all the computers that you use as well as share your customizations with your colleagues.
- Export a customized QAT:In the Customize the Quick Access Toolbar window, click Import/Export, then click Export all customizations, and save the customizations file to some folder.
- Import a customized QAT:In the Customize the Quick Access Toolbar window, click Import/Export, select Import customization file, and browse for the customizations file that you saved earlier.
- The file that you export and import also includes the ribbon customizations. Unfortunately, there is no easy way to export or import only the Quick Access Toolbar.
- When you import the customizations file to a given PC, all prior ribbon and QAT customizations on that PC are permanently lost. To be able to restore your current customizations in the future, be sure to export them and save as a backup copy before importing any new customizations.
That's how you customize and use the Quick Access Toolbar in Excel. I thank you for reading and hope to see you on our blog next week!