To make your experience with MS-Excel more complete and more precise, here are some of the advanced settings in excel which will help you to do more with your workbooks and enable you to create spreadsheets which are worth your data.
Advanced Settings in Excel
Changing the Action of Enter Button
Depending on your particular workflow you may want to insert the data in the row cells first.
But the default behaviour of the enter key is that it goes in downwards direction, changing the row. This may be frustrating as you may want to insert the data on the cells which are right to the current cells.
To change the behaviour of the enter key just go to the Advanced Tab in Excel Options dialog which we opened earlier.
In the dialog box, check the box if it is unchecked which says “After Pressing Enter, move selection” and select your desired direction to move.
Changing the number of recent files to show
If you are working with a large pool of files you may switch documents here and there.
One thing you can do for faster switching between the different excel documents is to include more lists of files in the recent files. This way you can choose from a large file set and switch faster to get along with your work.
Under the “Show this number of Recent Workbooks”, choose the number of files you want to display in the recent window.
Changing the Ruler Units
There are three ruler units to choose from. They are inches, centimeters and millimeters. Select the option which you are the most comfortable with.
Other Editing Options
- Automatically insert a decimal point – Check this box if you always want to display a number with decimal places.
- Places – Enter the number of decimal places in the Places box to indicate where Microsoft Excel automatically places the decimal point in the numbers that you type as constants on a worksheet. A positive number moves the decimal point to the left; a negative number moves the decimal point to the right. If the Places box is left blank or is set to 0 (zero), you need to enter the decimal point manually. To override this option, type a decimal point in the cell when you type the number.
- Enable fill handle and cell drag-and-drop – Select this check box to move and to copy cells and data by dragging. When this option is selected, you can also drag the fill handle to copy data and to fill adjacent cells with a series of data.
- Alert before overwriting cells – Displays a message if you drop cells over other cells that contain data.
- Allow editing directly in cells – Select this check box to enable editing inside a cell by double-clicking the cell, rather than always having to edit the cell contents in the formula bar.
- Extend data range formats and formulas – Select to automatically format new items added to the end of a list to match the format of the rest of the list. Formulas that are repeated in every row are also copied. To be extended, formats and formulas must appear in at least three of the five last rows preceding the new row.
- Enable automatic percent entry – Select to multiply by 100 all of the numbers less than 1 that you enter in cells that are formatted in the Percentage format. Clear this check box to multiply by 100 all of the numbers that you enter in cells that are formatted in the Percentage format, including numbers equal to or greater than 1.
- Enable AutoComplete for cell values – As soon as you start to enter data in a cell, if it matches a previous data it will autocomplete it, if the option is checked.
Changing the Start-Up workbook of MS-Excel
This is one of the advanced and useful tools to change the starting workbook of MS-Excel.
If you want to open a specific Excel file in which you work the most after starting up the MS-Excel Software you can do so by including your excel file to the XLSTART Directory.
The file needs to be saved or moved to your XLSTART directory which is on your local C: drive. The location of this directory varies depending on your version of Windows and Microsoft Office; search your hard drive for the folder. On most systems, the location is:
C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART, where <username> is your Windows login name.
To view the location of the XLSTART directory:
- Click on the File tab in Excel Home Screen.
- Click on Options
- Go to the Trust center
- Under Microsoft Excel Trust Center -> Click on Trust Center Settings
- Then click on Trusted Locations
You will find something like this from which you can locate the XLSTART directory and paste your working folder to the directory to start the file on every Start up.
If you are not able to find the folder, make sure you have enabled – show hidden files in your system.
Image Size and Quality Settings in Excel
You can check the box with label Do not compress images in file, after checking this box.
All of the images which you have inserted in the document will be of original quality and Excel will not compress the images. It helps when you want to print the document with high DPI or if you have a lot of information within the picture that needs to be shown crisp and clear.
Checking this option provides maximum picture quality but results in big file sizes. If you uncheck this button the images will be down sampled to the Default Resolution which is specified below when they are saved.
Under the Print section if you check High Quality mode for Graphics, then your document when printed, if it contains graphics will be of high quality. It will result in high quality print outs of documents which are recommended for infographics, brochures and presentation purposes.
Formulas
Under the advanced tab, the Formulas section provides settings for whether you want to Enable multi-threaded calculation. If this option is checked the system will utilise more cores of your processor if it is available when calculating complex formulas.
Enabling multi-threading can boost performance and give faster results at the expense of more power uses resulting in rapid battery loss if you are on a Laptop.
You can also manually select the number of cores available to excel. In this way you can balance the power and performance usage of the system.
Chart Options
 |
- Show chart element names on hover – Displays the name of a chart element when you rest the pointer over it.
- Show data point values on hover – Displays the value of a data point (data points: Individual values plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various other shapes called data markers. Data markers of the same colour constitute a data series.) when you rest the pointer over it.
- Properties follow chart data point for all new workbooks – (Added in 2013). Custom formatting and data labels follow data points as they move or change on all your charts in new workbooks.
- Current Workbook – (Added in 2013). Select the workbook in this list box that is affected by the following option.
- Properties follow chart data point for current workbook – (Added in 2013). Custom formatting and data labels follow data points as they move or change in a specific workbook.
General
- Ignore other applications that use Dynamic Data Exchange (DDE) – Prevents the exchange of data with other applications that use Dynamic Data Exchange (DDE).
- Ask to update automatic links – Displays a message that lets you confirm before linked items are updated.
- Show add-in user interface errors – Displays errors in the user-interface of add-ins that you install and use.
- Scale content for A4 or 8.5 x 11″ paper sizes – Microsoft Excel will automatically set your workbook content to fit in the specified paper size as different regions use different sizes, this option will help you to get perfect prints in any size specified.
- At start-up, open all files in – At start-up, Excel automatically opens files from the folder that you type in this text box. Type the full path to the folder in the text box to indicate the location of the files. Unfortunately, there is no folder browse button so you have to copy and paste the folder path.
- Web Options – Sets options for how Excel data looks and responds when the data is viewed in a Web browser.
- Enable multi-threaded processing – This controls all multi-threaded operations (except calculation). This includes sorting, row height calculations and finding the best fit for column widths. This option is not available in the object model. Selected by default but you need to have a multi-threaded processor.
Customise Ribbon
The picture above represents the ribbon in MS-Excel.
To customize the Ribbon.
Go to File->Options->Customise
You’ll get this window:
Now you can customize the ribbon according to your choice.
You can add a new tab with selected functionality. Or you can edit existing tabs to show the tools which you want or to hide some options.
What you can customize
To save your time and efforts when working on different tasks in Excel, you can personalize the ribbon with things like:
- Show, hide, and rename tabs.
- Rearrange tabs, groups and custom commands in the order you want.
- Create a new tab with your own commands.
- Add and remove groups on existing tabs.
- Export or import your personalized ribbon.
What you can’t customize
- Though a lot of ribbon customizations are allowed in Excel, certain things cannot be changed:
- You can neither change nor remove the built-in commands, including their names, icons and order.
- You cannot resize the ribbon, nor can you change the size of the text or the default icons. However, you can hide the ribbon completely or collapse it to show only the tab names.
- You cannot change the colour of the ribbon in Excel, but you can change the colour scheme of the entire Office.
When you customize your ribbon: Your customizations only apply to the Office program you’re working in at the time. For example, if you personalize your ribbon in PowerPoint, those same changes won’t be visible in Excel. If you want similar customizations in your other Office apps, you’ll have to open each of those apps to make the same changes.
Quick Access Toolbar
The Quick Access Toolbar is present at the top left section of the Excel Home screen.
You can quickly customize the toolbar by clicking on the drop-down arrow and selecting the options which you’d like to include in the quick access toolbar.
For advanced customisation to the Quick access toolbar.
Go to File->Option->Quick Access Toolbar
You can check the box which has the label Show Quick Access Toolbar below the Ribbon to show the toolbar below the ribbon. Default place is at the top left.
Add a command to the Quick Access Toolbar
- On the ribbon, click the appropriate tab or group to display the command that you want to add to the Quick Access Toolbar.
- Right-click the command, and then click Add to Quick Access Toolbar on the shortcut menu.
Add a command to the Quick Access Toolbar that isn’t on the ribbon
- Click Customize the Quick Access Toolbar > More Commands.
- In the Choose commands from list, click Commands Not in the Ribbon.
- Find the command in the list, and then click Add.
Hope you have found this article very useful.
Sources.
