You are reading the article Microsoft Excel Shortcut Keys And Their Functions updated in February 2024 on the website Kientrucdochoi.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Microsoft Excel Shortcut Keys And Their FunctionsKeyboard shortcuts in Microsoft Excel
However, we believe keyboard shortcuts tend to make using Excel much easier than the traditional method, so with that in mind, we have decided to list what we deem as the best Microsoft Excel shortcuts you can use right now.
Here is a list of all the important Keyboard Shortcuts in Microsoft Excel and their functions to help you get the best out of Excel.General Program Shortcuts in Excel
When it comes down to manipulating workbooks, getting help, and navigating the interface, the shortcuts below should help a great deal.
Ctrl+N: All about creating new workbooks.
Ctrl+O: This is for opening an existing workbook.
Ctrl+S: Use this shortcut to save a workbook.
F12: If you want to open the Save As dialog box.
Ctrl+W: When it’s time to close a workbook.
Ctrl+F4: Close Excel completely.
Shift+F11: If you want to insert a new worksheet then use this shortcut.
Ctrl+Z: Easily undo a previous action
Ctrl+Y: Easily redo a previous action.
Ctrl+F2: Want to switch to Print Preview? This shortcut will get the job done.
F1: Launch the Help pane.
Alt+Q: When you want to go to the “Tell me what you want to do” box.
F7: Check for bad spelling.
F9: For those who want to calculate every worksheet in all opened workbooks.
Shift+F9: Perform calculations of all active worksheets.
Alt or F10: Press this key if you want to turn off key tips.
Ctrl+F1: Do you want to show or hide the Ribbon? Press this key.
Ctrl+Shift+U: Collapse or expand the formula bar.
Ctrl+F9: Press this key to minimize the workbook window
F11: For creating bar charts based on data you’ve selected on a separate sheet.
Alt+F1: This one is for creating an embedded bar chart based on select data on the same sheet.
Alt+F: Want to open the File tab menu? This key will do it.
Alt+H: If you want to go to the Home tab, then select this key.
Alt+N: Open the Insert tab
Alt+P: Navigate to the Page Layout tab
Alt+M: Navigate to the Formulas tab
Alt+A: Navigate to the Data tab
Alt+R: Navigate to the Review tab
Alt+W: Navigate to the View tab
Alt+X: Navigate to the Add-ins tab
Alt+Y: Navigate to the Help tab
Ctrl+Tab: Easily switch between open workbooks
Shift+F3: Press these keys to insert a function
Alt+F8: Do this if you want to create, run, edit, or delete a macro
Alt+F11: Do you want to open the Microsoft Visual Basic For Applications Editor? Press these keys.Moving Around in an Excel Worksheet or Cell using Keyboard Shortcuts
These shortcuts make it easier to navigate a cell or worksheet.
Left/Right Arrow: Do this if you want to move one cell to the left or right
Ctrl+Left/Right Arrow: These shortcuts are for moving to the farthest cell left or right in the row
Up/Down Arrow: Move a single cell up or down
Ctrl+Up/Down Arrow: Do this if you want to move to the top or bottom cell in the column
Tab: Visit the next cell
Shift+Tab: Navigate to the previous cell
Ctrl+End: Navigate to the most bottom right used cell
F5: Navigate to any cell by pressing and typing the cell coordinate or cell name.
Home: Navigate to the cell to the far left in the current row
Ctrl+Home: Go to the beginning of a worksheet
Page Up/Down: Move one screen down or up in a worksheet
Alt+Page Up/Down: Move one screen to the left or to the right in a worksheet
Ctrl+Page Up/Down: Move to the next or previous worksheetKeyboard shortcuts to select Excel Cells
Use these shortcuts to easily select one or more cells.
Shift+Left/Right Arrow: Extend the cell selection to the right or left
Shift+Space: Highlight the entire row
Ctrl+Space: Highlight the entire column
Ctrl+Shift+Space: Highlight the entire worksheetKeyboard shortcuts to edit Excel Cells
Want to edit cells with keyboard shortcuts? Use these options.
F2: Edit a single cell
Ctrl+X: Do this if you want to cut the contents of a cell, selected data, or selected cell range
Ctrl+C or Ctrl+Insert: Press these keys if you want to copy the contents of a single cell, selected data, or selected cell range
Ctrl+V or Shift+Insert: Paste contents of a copied cell
Ctrl+Alt+V: Open the Paste Special dialog box
Delete: Easily remove the contents of a cell
Alt+Enter: Add a hard return within a single cell
F3: Paste a cell name, but ensure the cells are named in the worksheet
Alt+H+D+C: Delete an entire column
Esc: Cancel entries made in a cell or the formula barKeyboard shortcuts to format Excel Cells
Ctrl+B: Easily add bold to contents and remove it if needed
Ctrl+I: Add or remove italics to the contents
Ctrl+U: Add or remove underline to the content
Alt+H+H: Choose a fill color
Alt+H+B: Insert a border
Ctrl+Shift+&: Useoutline border
Ctrl+Shift+_: Terminate outline border
Ctrl+9: Hide all selected rows
Ctrl+0: Hide all selected columns
Ctrl+1: Launch the Format Cells dialog box
Ctrl+5: Remove or apply strikethrough
Ctrl+Shift+$: Add currency format
Ctrl+Shift+%: Add percent formatWhat is Ctrl F2 in Excel?
In Excel, pressing Ctrl + F2 opens the “Print Preview” window, which allows you to see how your worksheet will look when printed. This window displays the worksheet as it appears on a printed page, including any margins, headers, and footers you have set up. You can change the margins, scaling, etc., and then print the document.
Read: Microsoft Excel Document not saved errorWhat is the function of Ctrl-A, Z, and Y in Excel?
Ctrl + A is all about selecting all content. Ctrl + Z focuses on undoing an action. Ctrl + Y, then, is when the user wants to redo an action. While the first one helps when you want to apply to format or delete in bulk, the second comes in handy to get back to the previous state. Lastly, Ctrl + YRedoes the last undone action using the Ctrl + Z shortcut. Useful in case of accidental changes.
You're reading Microsoft Excel Shortcut Keys And Their Functions
Simpler and faster is always better in Excel. We start with some of my favorite shortcuts, then move on to using Excel for everyday situations such as finding random numbers for passwords, or random numbers within a range, or discovering how much your monthly payments will be for a car or home loan.
.2. Shortcut: Number formats
Which is faster—the mouse or the keyboard? Those who use both work faster than either one or the other alone. Try these shortcuts: After you enter a number or column of numbers, highlight the target cells and press…
Ctrl+Shift+! (exclamation point) to display the Number format with two decimal points,
Ctrl+Shift+$ (dollar sign) to format the selected cells as currency,
Ctrl+Shift+% (percent sign) to format as percentages,
Ctrl+Shift+~ (tilde) for the General format,
Ctrl+Shift+# (pound sign) for the Date format,
Ctrl+Shift+@ (at sign) for the Time format, and
Ctrl+Shift+^ (caret) for the Exponential number format. JD SARTAIN3. Shortcut: Quick Zoom
How about this simple shortcut instead: Hold down the Ctrl key and roll the mouse’s scroll wheel forward to zoom in or backward to zoom out. Easy.
Note: This shortcut also works in Word, Outlook, PowerPoint, Windows, and the Internet (i.e., Internet Explorer, Chrome, Mozilla Firefox, etc.).
.4. The PMT function
Ever wonder how much your house or car payment would be for a specified loan amount? Imagine having the tools to determine if you can afford a Prius or a Camry; a house or a condo? Say, for example, the house cost $200,000 for a 30-year loan at 3.2% interest? Use the PMT formula to find out. In cell A3, enter this formula: =PMT(3.2%/12,360,200000).
Note the answer is ($864.93). Why is it displayed as a negative number? Because it represents money that you pay out (as opposed to money you receive).
Now, instead of piling all the information into a single formula, place the data into separate cells so you can change the numbers and play around with the payment amounts. For example, enter the following field names in columns A, B, C, D, and E: Interest Rate, Divide by Months In the Loan Year, Term (in Months), Loan Amount, and Payment.
Now enter some data into these fields; for example: in A7 enter the current market interest rate. Next (in B7), if the rate is per year (which is almost always), enter 12 (for 12 months in the year. In column C, enter the term or length of the loan (in months, not years). Last, enter the loan amount in chúng tôi enter this formula in the Payment field (E7): =PMT(A7/B7,C7,D7). The answer is ($1100.65).
Now you can change the values in A7 through D7 (or copy the information down) and enter different interest rates, loan amounts, and terms to find out how much the payments are for your next house, car, boat, or any other loan.
Use the PMT function to calculate loan payments.5. The RAND function
If you’re one of those disciplined few who changes your password every week or month, or you manage the passwords of users on a network; the RAND function is your best friend because the numbers really are random. You can create a list in Excel, use this function to create the passwords, then pass them out to your staff. And because Excel recalculates formulas every time you press the Enter key and every time you save and exit, the numbers will never be saved anywhere.
NOTE: RAND numbers are between 0 and 1. You can remove the preceding decimal for your passwords.
6. The RANDBETWEEN function
The RANDBETWEEN function is just like the RAND function, except you get to choose the range of numbers—for example, between 1 and 500, or 10 and 1,000. Once you remove the decimals in the RAND command, the random numbers created are good for most all situations. But if you specifically want four-digit or six-digit numbers only, use RANDBETWEEN and choose a range of numbers that fall into that requirement.
Microsoft Excel is one of the most popular program with over a billion users. It is being used for creating charts, tables, reports, identifying trends, and arranging data, etc. Though, it comes with some brilliant functions that enhances productivity and after learning some useful tricks anyone can master Microsoft Excel, it has its own drawbacks as well. It is quite expensive and not available on all the platforms. Furthermore, it doesn’t have reliable collaboration and sync features, and it also lacks many customization options. Fortunately, Microsoft Excel is not the only worksheet creation tool available in the market. There are many other Office suites with Excel alternatives available both free and paid that can be a good replacement to Microsoft Excel in certain situations. You will find both online and offline tools, with unique features.Best Excel Alternatives 1. Google Sheets
Key Features: Collaboration tools, Auto save and sync and Completely free to use.
Get Google Sheets2. Numbers
There are no rows and columns when you start a sheet, instead you are given different templates to choose from (created by Apple itself). You can create amazing charts and tables catered for every need.
Key Features: Collaboration tools, great customization ability and free to use.
Compatibility: iOS and Mac OS X.
Get Numbers3. Microsoft Office Online (Excel)
If you are really looking for a copy of Microsoft Excel for free, then Microsoft Office online could be a great alternative. It is a Microsoft Office suite (Including Excel online) that is just online with minor addition of features, and lacks some power user features of the desktop app such as, macros, mail merge, Format Painter, shading, borders and the ribbon, etc.
It offers really good collaboration tools (may be not as good as Google Sheets), and saves all the documents to OneDrive or Dropbox. The saving and syncing is in real-time and you get some added search tools as well.
The main drawback is that it doesn’t have any kind of offline version. If you don’t have access to the internet, the app is pretty much useless. Apart from that, the programs works great and gives you similar feeling of Excel 2013. As it is web based, loading huge data can lead to lags and frustration.
Key Features: Give you similar feeling of the Desktop Microsoft Excel, real-time save and sync to OneDrive, Free and reliable collaboration tools.
Compatibility: Windows, Mac OS X, Linux and Android.
Get Microsoft Office Online4. Zoho Sheets
It has some robust set of collaboration tools; it saves and syncs sheets to Zoho’s cloud storage. Although, it has a free version, but it is limited. The free version will get all the required features along with 1 GB of storage. If you want more space and some other security and collaboration freedom, you will have to pay.
Key Features: Simple interface, reliable collaboration tools and both web and desktop clients.
Pricing: Free with limitation, and $5/month with 50 GB storage and $8/month with 100 GB storage and all features unlocked.
Get Zoho Sheet5. Smartsheet
Smartsheet is an amazing online alternative to Excel with features for power users and companies specifically. However, just like Excel this is not a free tool. You will just get a 30 days trial and after that, you will have to pay on monthly or yearly basis. Smartsheet can do almost anything Excel can do, but it adds some extra control and customization options.
Just like Apple’s Numbers, you will start with a blank document and you can use a template to start your work. The tool can be a bit complicated for some novice users, but there are tips and video instructions provided with each step. You can easily customize rows and columns with complete control over their visuals.
Key Features: Online access to sheets, Tons of customization options and great collaboration tools.
Pricing: Basic $16/month with 3 GB storage, Team plan $50/month for 3 users with 45 GB storage and Customized plan with up to 25 users and 375 GB space for a price of $270.
Compatibility: Windows, Mac OS X, Linux and Android.6. Apache OpenOffice Calc
However, it is not a head-to-head competitor to Microsoft Office or Excel due to its limited features and a bit complex instructions. The interface is not that bad and shows easy to gulp information, but the options are explained quite poorly. It does offer many features like templates, mail merge, style manager, headers and footers, frames and many others.
Key Features: Free and Open-source, Desktop client just like Excel and comes with a clean interface.
Compatibility: Windows, Mac OS X and Linux.
Get OpenOffice7. LibreOffice Calc
The interface is quite similar to old version of Microsoft Office like Office 2003, but the features are powerful enough to fight against the latest versions. It has some amazing import and export features and is said to import almost any document format. It is also known for opening obsolete versions of Microsoft office documents that people don’t even use now.
Some of its unique features include, Portable version, different macros language, multi-platform support, extensions and import graphics formats.
Key Features: Open-source and free, powerful features for a free software, Desktop client for easier offline working.
Compatibility: Windows, Mac OS X, Linux and a viewing app for Android.
Get LibreOffice8. Gnumeric
Gnumeric is another addition of free to use Open-source tool in this list. As the name suggests, Gnumeric works best for handling numeric data and doing all the calculations. If you use Excel for managing accounts and other similar things, then Gnumeric might be a better and faster choice.
Its calculations are tested by the professionals and they are as accurate as Excel with much faster results and processing. If you are looking for a free and fast alternative to Excel calculations, Gnumeric should be a happy replacement.
Key Features: Light and Accurate, Simple interface and Open-source.
Get Gnumeric9. PlanMaker
PlanMaker is actually designed to be a cheaper alternative to Excel, so if you need something similar to Excel but at a cheaper rate, PlanMaker is all open. Although, it is still not better than Microsoft Excel, but it is very close and interacts quite well with Excel documents.
Moving documents from Excel to PlanMaker will never lead to change in data structure. It supports even older versions of Microsoft Excel docs, like .xls sheets. The interface is also quite similar to Excel, so you will not get stuck with learning new things.
Key Features: Interacts well with Excel Sheets, a cheaper alternative to Excel and makes Excel users feel like home.
Pricing: Free 30 trial, and Standard version for €69.99 and Professional version for €89.95.
Compatibility: Windows, Mac OS X, Linux and Android.10. WPS Office Suite
If you are big on editing sheets on your smartphone, then you might like to try out WPS Office Suite. It is an Excel alternative for your Smartphone that makes editing a zippy. Of course, it can’t really compete directly against the desktop app, but it makes it very simple to make minor to average edits from your phone for free.
It integrates with Dropbox to save all your documents and sync them over all your devices. Best of all, it doesn’t require any kind of account, unlike Microsoft Excel or Google Sheets for smartphone.
Compatibility: Android, iOS and Linux.
Get WPS Office Suite
The recording and playback features will allow the developers to record all the keyboard keys and later play/replay them, as and when required.
This will help the developers to automate and replay certain keystrokes repeatedly during testing of the code for various performance testing needs, as well as while debugging a project and automate multiple steps required to do the needful.
The steps to get the Key Recording done are:
Start the Recording
Navigate through the sequence of key strokes
Dump the Recorded information
Stop the Recording
The dumped recorded information when opened in Textpad, is as shown below:
As seen in the figure, the macros are appended to the file chúng tôi within the application path. Comma-separated Keys are written in the file against each macro name separated with the separator symbol specified while dumping the macro from the memory to the file.
If the Macro gets recorded beyond 4000 characters, the Function $$FileRead cannot read further, which means that Keys beyond 4000 characters cannot be played back.
Hence, the Macro Recording capability has been enhanced in such a way that after recording 4000 characters, a new macro gets created automatically, and the subsequent keys are recorded in the new macro. The new macro will bear the same macro name, with a number concatenated to it. All the Macros created will be dumped in a single file, and replayed when required. Thus, n number of macros can be recorded in a file, read and played back.
For example, if the Name of the Macro is Testing, then after every 4000 characters, a new Macro is created with the Name Testing-1, Testing-2,…Testing-n. Thus, there is no limit to the number of characters which can be recorded, read and played back.
The following Calculator Pane Commands are supported for Key Recording and Playback:
Record Mode gives various commands to help the developer in recording keys. The mode should be set by specifying the mode as Record.
MODE : Record
The following commands can be specified in the Calculator Pane once the Mode is set to Record:
This command is used to start recording a macro.
START : BSView
It is used to replay the recently recorded macro from the memory, but before the same is dumped into a file. If the macro is already dumped into a file, the same is cleared from the memory and will not be available for Replay. The macro from the file can then be replayed using File I/O Approach and Action ‘Trigger Key’ (explained later), but cannot be replayed through Calculator Pane.
This command is used to pause the recent recording.
This command is used to resume the recently paused recording.
This command is used to dump the recording to the file chúng tôi The macro name will be separated from the keys with the ‘separator character’ specified as the parameter. It also clears the keys in memory.
This command is used to stop recording the recent macro.
This command is used to list the macros which are recorded and available in the memory. The dumped macros will not be listed as they are cleared from the memory as soon as they are dumped in the file.
This command provides a list of all the recording commands, with description of their purpose.
To reset command prompt to regular behaviour, you need to mention MODE.
To perform all the above actions or Calculator Pane Commands, Tally must be executed in DevMode.
Apart from Calculator Pane Commands, there are several TDL Actions provided to programmatically execute the Key Recording operations. They are:
This action is used to start recording every key entered in the memory, with the specified macro name. In the absence of optional parameter Macro Name, default name assumed will be ‘Macro’.
[Button: Start Recording]
Title : “Start”
Key : Alt + C
Action : Start Recording : “BS View”
This action pauses the recording, which can be resumed further. For instance, while recording multiple Vouchers, we might have to run through the Report to check the Number of vouchers, Amount, etc., and then resume recording the Vouchers.
[Button: Pause Recording]
Title : “Pause”
Key : Alt + U
Action : Pause Recording
This action resumes the paused recording.
[Button : Resume Recording]
Title : “Resume”
Key : Alt + M
Action : Resume Recording
This action dumps all the recordings to a file with the given separator. Each recording is dumped with its name and keys. This also clears the keys in memory.
[Button : Dump Recording]
Title : “Dump”
Key : Alt + G
Action : Dump Recording : “BSView” : “,”
From Release 4.7 onwards, the Action ‘Dump Recording’ has been enhanced to write the macros to a File specified by the user. The Action ‘Dump Recording’ will accept 2 optional parameters, viz. File Name and Separator.
If the File Name is left unspecified, then by default, the Action would dump the recording to the file Macros.log.
If the Separator is left unspecified, then by default, the system would consider Tilde (~) as the default separator.
[Button : Dump Recording]
Title : “Dump”
Key : Alt + U
Action : Dump Recording : “BSView.txt” : “-”
The behaviour of Calculator Pane Command Dump is retained as in Release 4.6, i.e., it will create a file with the name chúng tôi in the Tally Application Folder.
This action stops the recording.
[Button : Stop Recording]
Title : ” Stop”
Key : Alt + N
Action : Stop Recording
The recordings once dumped in a file against a name using the above actions, can be replayed by reading the file using File I/O approach and Triggering the keys in a loop using the Action ‘Trigger Key’, which will be covered ahead.
When the macro keys are recorded using Key Recording Actions or when they are dumped into the Macros File from the Calculator Pane Command; in order to play them back, one needs to make use of the Action Trigger Key, which sends the list of keys in sequence to the system as if an operator is pressing those Keys. The Keystrokes of a required macro can be copied from Macro Log file and pasted against the ‘Trigger Key’ Action, which triggers all those Keys in sequence as required.
Trigger Key : V, F5, Enter, “Cash”, Enter, “Conveyance”, Enter, “50”, Ctrl+A
When the macro keys are recorded using Key Recording Actions or when they are dumped into the Macros File from the Calculator Pane; in order to play them back, one needs to make use of the Action Trigger Key, which sends a list of keys in sequence to the system as if an operator is pressing those Keys.
Trigger Key : DD, Enter : 5, “Item 1”, Enter
This is the same as:
Trigger Key : DD, Enter, Enter, Enter, Enter, Enter, “Item 1”, Enter
Following happens when the above Action is invoked from Gateway of Tally:
The First D navigates us to Display.
The Subsequent D navigates us to Daybook.
Enter:5 triggers the Enter Key 5 times, i.e., Drills down into the current voucher, accepts 4 subsequent non-skipped fields, and moves to the 5th non-skipped Field in the Voucher.
In the Fifth field, the text Item 1 is entered.
The Subsequent Enter then accepts the current field, and the focus is shifted to the next non-skipped field.
Trigger Key : DS, Enter:3
The following function has been introduced for key recording:
This function is used to check the status of the recorder. It returns a String value to indicate whether it is in Started, Stopped or Paused mode.
When you want to display different data sets visually, you can create a combination chart. If you want to show something like sales with costs or traffic with conversions, a combo chart in Microsoft Excel is ideal.
We’ll show you how to create a combo chart in Excel as well as customize it to include the elements you need and give it an attractive appearance.
Table of ContentsHow to Create a Combo Chart in Excel
You have a few ways to create a combo chart in Excel. You can convert an existing chart, select a quick combo chart type, or set up a custom chart.
Convert an Existing Chart to a Combo Chart
If you already have a chart showing your data, like a bar chart or even a pie chart, you don’t have to delete it and start from scratch. Simply turn it into a combo chart.
Select your current chart and go to the
Change Chart Type
in the Type section of the ribbon.
When the chart window opens, pick
on the left.
Then, select one of the combo chart layouts at the top and customize the series at the bottom.
and you’ll see your new chart replace the original one.Select a Quick Combo Chart Type
Excel offers three combo chart types that you can pick from for your data.
Select your data set and go to the Insert tab.
In the Charts group, choose the Insert Combo Chart drop-down arrow to see the options. Pick from a clustered column with a line chart, a clustered column and line chart with a secondary axis, or a stacked area and clustered column chart.
Create a Custom Combo Chart
If you don’t have an existing chart and prefer to customize the series and axis for the combo chart from the start, you can create a custom chart.
Select your data set and go to the
Insert Combo Chart
drop-down arrow and pick
Create Custom Combo Chart
When the chart window opens, you’ll see four combo chart types at the top. Select one of these as the base for your custom chart.
At the bottom, you’ll see your data series, chart types, and options to pick a secondary axis. As you make your adjustments, you’ll see a nice preview of the combo chart directly above.
While a column chart and line graph work well together, you can choose a different chart type for each data series if you like. Select the drop-down box below
to the right of the series you want to change and select the new chart type.
By default, the first data series displays on the primary axis. However, you can change this or simply add a secondary axis using the
check boxes on the right side.
When you finish creating your custom combo chart, pick
to save it and place it on your worksheet.How to Customize a Combo Chart
Once you choose and insert your combo chart, you may want to add more elements or give the chart some pizzazz. Excel offers several features for customizing a chart.Go to the Chart Design Tab
For basic appearance features and chart elements, select your chart and go to the Chart Design tab.
Starting on the left side of the ribbon, you can use the Add Chart Element drop-down menu to add and position items like the chart title, data labels, and legend.
To the right, use the Quick Layout menu to change the layout to include and position elements without having to do so one-by-one.
In the Chart Styles section, you can use the Change Colors drop-down menu to pick a different color scheme or the Styles box to choose a whole new design.
With the remaining options on the ribbon, you can switch columns and rows, change the chart data selection, pick a new chart type, or move the chart to another sheet.
Open the Format Chart Area Sidebar
Use Chart Options or Text Options at the top of the sidebar depending on which item you want to change. You can then use the tabs directly beneath to make your changes.
Chart Options: Change the fill and border styles and colors, add effects like a shadow or soft edge, and set the size or position for the chart.
Text Options: Change the fill or outline styles and colors, add effects, and position or align the text.Use the Chart Buttons (Windows Only)
One more way to make adjustments to your chart is to use the buttons that display on the right side of it. These are currently only available in Microsoft Excel on Windows, not Mac.
Chart Elements (plus sign): Like the Chart Elements drop-down box on the Chart Design tab, you can add, remove, and position items on the chart.
Chart Style (paint brush): Like the Chart Styles section on the Chart Design tab, you can pick a different color scheme or style for your chart.
Chart Filters (filter): With this button, you can check or uncheck the details in your dataset that you want to display on your chart. This gives you a quick way to view only specific chart data by hiding other details temporarily.
Now that you know how to create a combo chart in Excel, look at how to make a Gantt chart for your next project.
The real power of Microsoft Excel lies in its formulae. However, as a Microsoft Excel user would know well, making mistakes with formulae are common since they are complicated. You can fix this by tracing these errors and checking suggestions for improvements. This is called Background error checking. We will show you the procedure to enable or disable the same.What is Background error checking in Excel?
Microsoft Excel is not only about arranging data in sheets. The real purpose of the software is to make calculations. Microsoft Excel is used to create information out of random data. Formulae are used for this purpose. Since formulae in Microsoft Excel can get very complicated, it is important to check them for errors. When Microsoft Excel does this process automatically in the background, then it is called Background error checking.How to enable or disable Error checking rules in Microsoft Excel
Background error checking is enabled on Microsoft Excel by default. However, if a third-party extension disabled it or another user disabled the option, it can be re-enabled again as follows:
From the menu, select Options.
Go to the Formulas tab.
In the Error checking section, check the box associated with Enable background error checking.
If you wish to disable Background error checking, then simply uncheck the box.
If the enabling or disabling or Background error checking is caused due to a third-party extension, you will have to disable the same.How to change the color for Background error checking in Excel?
The color for errors marked through the Background error checking process are all green. This is because green is the default color for these marked errors. You can change this color as well. The procedure is as follows:
Go to the Formulas window as explained earlier.
In the Error checking group, you will find an option for Indicate error using this color.
Change the color from the drop-down menu.How to reset Ignored errors in Microsoft Excel?
The Background error-checking process marks errors as per a set procedure. Many errors marked by the process could be genuine formulae or instructions. In this case, you can ignore them or whitelist them. Should you be willing to reset this list of Ignored errors, then the procedure is as follows:
Go to the Formulas window as explained earlier.
Scroll down to the Error checking section.
You will notice a button for Reset Ignored Errors.How to modify Error checking rules in Microsoft Excel?
The Background error checking is dependent on a set of rules which can be modified. Basically, these rules indicate which figure or formula will be marked as an error. The procedure to change these error-checking rules is as follows:
Go to the Formulas window as explained earlier.
You will notice options with checkboxes.
Check a box to enable the associated option and uncheck the box to disable the associated option.How to change error checking rulesin Microsoft Excel?
The meanings of the error checking rules are as follows:1] Cells containing formulas that result in an error
Whenever the error is with the syntax of the formula itself, then this rule comes into play. It is marked with #VALUE! or #DIV/0! .2] Inconsistent calculated column formula in tables
This rule marks cells in which the syntax of the formula may be correct, but the formula might be inconsistent with the column. Eg. If you mark a column which doesn’t fit in the formula, then you will get an error.3] Cells containing years represented as 2 digits
Years should be represented as 4 digits. Some people prefer them as 2 digits. This rule will mark the cell if the year is marked as 2 digits. If you did this intentionally, then you can consider unchecking the checkbox associated with this rule.4] Numbers formatted as text or preceded by apostrophe
Writing ten and mentioning 10 is read differently by Microsoft Excel. Similarly, writing 10 and “10” is read differently by Excel. Anything other than numerical representation of numbers is not read by formulas.5} Formulas inconsistent with other formulas in the region
When you use lots of formulas in Microsoft Excel, eventually, formulas become dependent on each other. In many cases, the value obtained by one formula is used by another one. In this case, the mentioned rule will mark an error if a formula you created in not in conjunction with other formulas of the region in the Microsoft Excel sheet.6] Cells containing data types that couldn’t refresh
A lot of data in Microsoft Excel is picked from other sources. As an example, you can add stock market data to Microsoft Excel. Since this data keeps changing, Microsoft Excel keeps refreshing the data and uses that data for other calculations. However, imagine a case where the internet is not working, or the server of the stock market is down. In this case, the data will not refresh. This rule will mark an error to indicate the same.7] Formulas which omit cells in a region
Formulas may or may not influence all cells in a region. However, if they do not impact each cell, the mentioned rule will come to play. Should this be done intentionally by you, the rule can be left unchecked.8] Formulas referring to empty cells
An empty cell carries a default value of 0 but that might not always be the intention for keeping the cell empty. If a cell included in a formula is empty, it will be marked if this rule is active.9] Data entered in a table is invalid 10] Misleading number formats
When you read dates, time, etc, they use a specific format. This is called number format. The Misleading number format rule can help with marking such number formats.Why does Background error checking in Excel keep disabling on its own?
A lot of functions in Microsoft Excel are managed by its extensions. These extensions can also manage the Background error checking and disable the option. Merely re-enabling it will not solve the problem for long-term. You can disable the problematic extension as follows.
Open Microsoft Excel.
In the left pane, go to the Add-ins tab.
Corresponding to the drop-down menu associated with Manage, select COM Add-ins.
Try this method till your problem gets fixed. You will be able to figure the problematic extension using the hit and trial method.How do I enable error checking in Excel?
To enable error checking in Excel, you need to follow the steps as mentioned above. Open the Excel Options panel first and switch to the Formulas tab. Then, find the Enable background error checking option and enable it to turn the feature on. You can also follow the article for a detailed guide.How do you enable the Formulas referring to empty cells in the error checking rules?
As of now, there is no option to enable or disable this functionality in Microsoft Excel. However, if you want to turn on or off error checking in the same app, you can go through the above-mentioned steps. For your information, you can set various conditions to enable or disable the error checking.
Update the detailed information about Microsoft Excel Shortcut Keys And Their Functions on the Kientrucdochoi.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!