«Home

What excel spreadsheets hacks we all need to know?

How to hack the password of Excel files if you forget it? How to copy files without violation of format? Read the article to learn the best hacks.

Excel hacks

Excel hacks

  • How to convert PDF into Excel? Load your PDF, having drawn it, or just having pressed the button to choose the file. Now PDF is converted into the edited table, and your task is only to download it. Remember that some conversion tasks are available only paid.
  • Flash Fill. This function has appeared only in the latest version of Excel, but it is worth to be updated to a new version. Assume that you have a list of full names, which you should turn in reduced. To do such transformation, it is necessary just to begin to write the desirable text in the next column manually. On the second or the third line, Excel will try to foresee our actions and will execute further processing automatically. It will be necessary only to press the Enter key for confirmation, and all names will be transformed instantly.
  • Copying without violation of formats. Most likely, you know about magic marker of auto filling — a thin black cross in the right bottom corner of box, having pulled for which it is possible to copy contents of the box or formula on several boxes at once. However, there is one unpleasant nuance: such copying often breaks design of the table because not only the formula, but also box format is copied. It can be avoided if to press the smart tag right after drawing with black cross. It is a special badge appearing in the right bottom corner of the copied area. If to choose the option 'Fill without Formatting', Microsoft Excel will copy your formula without format.

READ ALSO: How to make a business plan?

Microsoft Excel

  • Display data from the Excel table on the card. In the latest version of Excel, an opportunity to display your geo data quickly on the interactive map has appeared, for example, sales on the cities, etc. For this purpose, it is necessary to pass into Office Store on the Insert tab and to install Bing Maps plug-in from there. It can be done also with the direct reference from the website, having pressed the Add button. After addition of the module, it can be chosen in the dropping-out 'My Apps' list on the Insert tab and placed on your work sheet. It will be necessary to allocate your boxes with data and to press the 'Show Locations' button in the card module to see your data on it.
  • Quick transition to the necessary page. If the number of work pages in your book became more than 10, it is difficult to be guided in them. Click on any button of scrolling of labels of sheets in the left bottom corner of the screen. There will be a table of contents, and it will be possible to pass to any necessary page instantly. It is also possible to create table of contents with hyperlinks on single page. It is slightly more difficult, but it is often more convenient.
  • Transformation of lines to columns and back. If you are necessary to shift boxes from lines in columns, you will estimate the following trick. Allocate range. Copy it (Ctrl + C) or having pressed the right button of mouse, choosing Copy. Click on the box where you want to insert data, and choose one of options of special insert in context menu (Transpose). In old versions of Excel, there is no such badge, but it is possible to solve a problem with the help of special insert (Ctrl + Alt + V) and choice of the option ‘Transpose’.
  • The dropping-out list in the box. If in any cell, it is supposed input strictly certain values from the allowed set (for example, only 'yes' and 'no' or only from the list of departments of the company etc.), it can be organized easily by means of the dropping-out list: Allocate the box (or its range), in which there has to be such restriction. Press the 'Verification of Data' button on the Data tab (Data — Validation). Choose 'List option' in the dropping-out list 'Allow'. Set the range containing reference options of elements, which will drop out subsequently at input in the field 'Source'. Advanced tricks from the same series are: the dropping-out list with filling, the connected dropping-out lists, the dropping-out list with the photo, etc.

Excel spreadsheet password hack

  • Smart table. If to allocate range with data and to press 'Home — Format as Table' on the Main tab, your list will be transformed to the smart table, which is able to do a lot of useful things and to stretch automatically when you add new lines or columns. The entered formulas will be copied by automatic machine on all columns. The cap of such table automatically is fixed when scrolling, and filter buttons for selection and sorting turn on. On the appeared 'Design' tab, it is possible to add a line of results with automatic calculation to such table.
  • Sparklines are the tiny charts drawn directly in boxes, which visually display dynamics of your data. To create them, press the Line or Columns button in group of Sparklines on the Insert tab. Specify range with basic numerical data in the opened window and boxes where you want to create Sparklines. After pressing OK button, Microsoft Excel will create them in the specified boxes. On the appeared Design tab, it is possible to adjust their color, type, to include display of the minimum and maximum values etc.
  • If you have Excel 2010, press File — Recent and find the ‘Recover Unsaved Workbooks’ button in the right bottom corner of the screen. In Excel 2013, the way is another: File — Properties — Recover Unsaved Workbooks. The special folder from Microsoft Office, where temporary copies of all created or changed files are located, will open.
  • Excel spreadsheet password hack. Office Password Recovery is an irreplaceable for selection of the password to Excel spreadsheets. For increase in speed of selection of the password, this program can connect the graphic processor of the video card (GPU). Speed of selection of the password increases up to 40 times. You can download it online.

Avatar
Newbie
0 comments
Your comment
Add image

By posting your comment, you agree to the privacy policy and terms of service.