From now onward, whenever you start Excel it will directly open the workbook without showing the start-up screen. So, you need to enable it from settings.
When you open a macro-enabled file, you need to enable macro options to run VBA codes. Follow these simple steps:. If you do a lot of data entry in Excel, then this option can be a game-changer for you.
With the auto-correct option, you can tell Excel to change a text string into another when you type it. Just think like this, you have a list of 10 products which you sell. Whenever you need to insert those product names you can insert them using a custom list. Let me tell you how to do this:. Now, to enter the custom list you have just created, enter the first entry of the list in the cell and then drill down that cell using the fill handle.
If you are not happy with the default color of cell grid-lines then you can simply change it with a few clicks and follow these simple steps for this:. This is one of my favorite one-time set up to save time in the long run.
The thing is instead of going to the start menu to open Microsoft Excel, the best way is to point it to the taskbar. If you have a macro code that you need to use frequently. Well, the easiest way to run a macro code is to add it to the quick access toolbar. So instead of selecting each cell one by one, you can select all the cells where you have a formula. To do some one-time calculations you can use the paste special option and save yourself from writing formulas.
Well, you can use a VBA code to highlight values but the easiest way is to use conditional formatting. Here are the steps you need to follow:. If you ever noticed that when you select a range of cells in Excel, a small icon at the bottom of the selection appears.
When you click on this icon you can see some of the options which are there on the ribbon which you can directly use from here to save your time. There is an option in Excel which you can use to open a specific file s whenever you start Excel in your system. Here are the steps. The idea behind this option is to get information by seeing definitions, images for the topic text from different online sources.
Sometimes you need to add screenshots to your spreadsheet. And for this, Excel has an option that can capture the screen instantly, and then you can paste it into the worksheet. If you use Excel to Excel , then you can locate a keyboard shortcut by pressing the ALT key.
Once you press it, it shows the keys for the options which are there on the ribbon, just like below. In the same way you can reach to all the option using the shortcut keys. Check out these selected 21 tips out of Serial Numbers. Current Date and Time. Sort Buttons. Drag and Drop. Status Bar. Paste from Clipboard. There is a problem with normal copy-paste that you can only use a single value at a time.
Add Bullet Points. Under the number tab, select custom. In the input bar, enter the following formatting. Copy of Worksheet. To create a copy of a worksheet in the same workbook drag and drop in the best way.
Undo-Redo Buttons. First of all, you need to add it to the quick access toolbar here are the steps. It will show you a window to select the formatting type and after selecting that click OK. Format Painter. Cell Message. Here are the steps to do this: First, select the cell for which you want to add a message. In the data validation window, go to the Input Message tab.
Month Name. Highlight Blank Cells. Font Color with Custom Formatting. Theme Color. Clear Formatting. Sentence Case.
Random Numbers. Count Words. Calculate Age. Root of Number. Days in Month. Smooth Line. Chart Formatting. Hide Axis Labels. Display Units. Round Corner. Select your chart and open formatting options. In borders sections, tick mark rounded corners. Hide Gap. Show the gap for the empty cell.
Use zero. Connect data points with the line. Here are the steps to use these options. Pictograph in Excel. Chart Template. Default Chart. Click on the arrow at the bottom right corner.
Click OK. Hidden Cells. Select your chart and right click on it. Print Titles. Now in the page setup window go to the sheet tab and specify the following things. Page Order. Specifying the page order is quite useful when you want to print large data. Now here, you have two options: The First Option: To print your pages using a vertical order. The Second Option: To print your pages using a horizontal order. Print Comments. Scale to Fit. Specify the number of pages in which you want to adjust your entire data using width and length.
Custom Header-Footer. Click on the custom header or footer button. Center on Page. Horizontally: Aligns data to the center of the page. Vertically: Aligns data to the middle of the page. Print Area. Simply go to the. Custom Margin. Error Values. You have three options to use as a replacement. Blank Double minus sign. Custom Start Page Number. Tracking Important Cells. Flash Fill. Combine Worksheets. Protect a Workbook. Adding a password to a workbook is quite simple, here are the steps.
Live Image. Select the range you want to paste as an image and copy it. Go to the cell and right-click, where you want to paste it. Custom Tab. Goal Seek. Text to Speech. Click here to read. Named Range. Remove Duplicates. First, select any of the cells from the data or select the entire data. Combine Text Fill Justify. Remove Specific Character. All you need to do is select that column and open the find and replace dialog box.
Combine Text. Unpivot Data. Delete Error Cells. In the Go To dialog box, select formula, and tick mark errors. Arrange Columns. Convert to Date. Negative to Positive. Rename a Worksheet. Fill Handle.
Hide Ribbon. If you want to work in a distraction-free mode, you can do this by collapsing your Excel ribbon. Edit a Shape. Column Width. Go to the Last Cell. Pivot Table Double Click. Right-Click Menu. Default File Saving Location. First, go to the File tab and open Excel options. Disable Start Screen. Developer Tab. Now from the tab list, tick mark the developer tab and click OK. Enable Macros. AutoCorrect Option. Here in this window, you have two input bars to specify the text to replace and text to replace with.
Custom List. Now in this window, you can enter the list, or you can also import it from a range of cells. In the end, click OK. Apply Table. Gridline Color. Pin to Taskbar. Macro to QAT. Select Formula Cells.
Highlight Duplicate Values. Here are the steps you need to follow: First of all, select the range of where you want to highlight the duplicate values. Quick Analysis Tool. How to hide Excel data and alert readers It's common to hide Excel data. If you want viewers to see that data, you can add a friendly alert.
How to work with different views in Microsoft Excel You can change views in Excel depending on what you're doing and what you want to see. Learn how in this step-by-step tutorial.
Entering leading zeroes in Excel By default, Microsoft Excel won't display or store leading zeros. Read this Excel tip to learn three handy ways to get around the problem. How to suppress 0 values in an Excel chart There isn't a one-size-fits-all solution for removing 0 values from an Excel chart. Here's a look at a few methods. Use a custom format in Excel to display easier to read millions Use a custom format in Microsoft Excel to reduce the number of digits in a large number without losing its scale.
Two ways to build dynamic charts in Excel Users will appreciate a chart that updates right before their eyes. In Microsoft Excel and Excel , it's as easy as creating a table. In earlier versions, you'll need the formula method. How to use Excel's find feature to highlight or delete matching values Excel's Find feature offers more than just finding values if you know the right steps. Learn how to put it to use in your spreadsheets. How to split a column using an IF function in Excel If you need to conditionally split values into multiple columns in Microsoft Excel, consider using the IF function.
How to combine Excel's VLOOKUP function with a combo box for enhanced searching A combo box's autocomplete feature linked to a lookup function creates a flexible search tool. How to subtotal transactions by conditional date components in Excel If you need a monthly or yearly balance for revenue transactions in Microsoft Excel, you're in luck because both are easy!
Here's how to find the totals you need. How to do more advanced averaging in Excel Averaging values in an Excel sheet is easy. But perhaps you want to ignore zeros or include multiple sheets. Knowing how each averaging function works is the key to choosing the right one.
How to average with and without the highest and lowest values in Excel Averaging in Microsoft Excel is easy, until you start excluding specific values. Here are three ways to average a data set when giving special consideration to the highest and lowest values. How to calculate bonuses and commissions in Excel Everyone likes a bonus, but sometimes calculating one can be a bit complicated--at first.
Here's how to calculate the amount in Microsoft Excel that you or your staff will get paid. How to find the minimum and maximum values within a specified set of years in Excel There's a lot going on in these formulaic conditional rules in Microsoft Excel that highlight the smallest and largest values within a period of years. How to copy expressions without changing cell references in Excel Microsoft Excel updates cell references when you copy an expression. Here are a couple of workarounds for those rare occasions when you don't want to change the cell references.
How to expose expressions that return a defined error value in Microsoft Excel You can't distribute an Excel sheet that displays ugly error values when something goes wrong! Learn how to expose those values while you work, so you can determine the right fix. How to highlight unique values in Excel The easiest way to spot a unique value is to format it, and Microsoft Excel offers two ways to do so. Use Excel to calculate the hours worked for any shift With Microsoft Excel, you can create a worksheet that figures the hours worked for any shift.
Follow these step-by-step instructions. Four ways to protect your Excel formulas free PDF If a user inadvertently changes the formulas in your Excel workbooks, all your hard work could go out the window.
This ebook explains how to protect and hide those formulas so they remain safe and intact. A super easy way to generate new records from multi-value columns using Excel Power Query Have a complex Excel problem?
Power Query to the rescue! Instead, use these 9 shortcuts to enter and work with expressions more efficiently. How to use conditional formatting to highlight due dates in Exce l The ramifications of missing a due date can range from simply adjusting the date to getting fired. Don't take chances with deadlines when a simple conditional format can remind you. How to use the selected value in a combo to determine conditional formatting in Excel Thanks to the linked cell property in Microsoft Excel, it's super easy to use the selected value as a condition in a macro or conditional formatting rule.
How to highlight the top n values in a Microsoft Excel sheet This formulaic conditional formatting Excel rule will let viewers determine how many top values to view on the fly.
How to change an Excel conditional format on the fly Conditional formatting is a flexible and powerful tool in Microsoft Excel, but you can't change a condition without modifying the underlying rule. Don't let that stop you—use an input cell. How to use Excel's conditional formatting to compare lists Whether you're comparing a single list or several, Excel's conditional formatting can get the job done. How to avoid a conditional formatting rule in Excel that doesn't work as expected Formulaic conditional formatting rules in Microsoft Excel can be tricky, so learn how to avoid a common mistake.
How to enter data quickly in Excel Microsoft Excel users, follow these two easy methods to reduce data entry time and errors when customized features aren't available. How to extract the date and time from a serial date in Excel If you have to work with a date stamp in Microsoft Excel that includes date and time, you can use these simple expressions to extract both components, making them easier to work with. Here's how to do both. How to use Find All to manipulate specific matching values in Excel Filters are great tools, but you can't remove specific items from the results.
When you need to do this, try Find All in Excel instead. When that happens, consider using Flash Fill to save time and aggravation. How to use sheet view for more flexible collaboration in Excel Whether you're tired of losing your spot when collaborating or you want to customize the way you view your data, sheet views in Microsoft Excel are for you.
How to use shortcuts to sort in Microsoft Excel If you run sorts in Excel a lot, you might benefit from shortcuts or even a macro. Here are some ways to make your sorting life easier. How to evaluate the last rows in a changing data set in Excel In Microsoft Excel, changing the evaluated range on the fly requires a bit of work, but it's definitely possible thanks to the Offset function. How to password protect an Excel workbook At the file level, you can password protect an Excel workbook in two ways: You can determine who can get in and who can save changes.
How to transfer data from Word forms to an Excel worksheet Avoid the hassle of manually importing Word form data into Excel. With the help of an Excel wizard, you can quickly step through the process.
0コメント