How To Clean Data In Excel Spreadsheet?

As most of the user, uses Excel spreadsheet but sometimes the user’s wants to clean the data in Excel. So, in this blog you will read how to clean data in Excel. For more information about Excel, just click on www.office.com/setup.

Method To Clean Data in Excel Spreasheet:

1: Clear Duplicate Data In Excel:

For this, first you have to tap in the Excel Spreadsheet. After this, you need to tap on Table Tools. Then, you have to click on Design. At this point, you should click on Remove Duplicate. At last, you should select the column which has duplicate data and then tap on OK button.

2. Text To Column Feature:

You should select the text which you wish to split into multiple cells. Then, you should tap on the Data tab. Now in Data Tools, you should select Text to Columns. Here in Convert Text to Columns Wizard, you should select the Delimiters checkbox. At this point, you should tap on the Next button. Then, just under Delimiter, you should select the Space checkbox and just leave the rest of the checkboxes. At last, tap on the Finish button.

3. Convert Numbers:

First, you need to type 1 in any blank cell. After this, where you have typed 1, just select that cell and tap on Ctrl + C together. Now, you have to select the cells which you want to convert into numbers. Then, you need to right-click and then select Paste option. After this, you need to select Paste Special in order to open the Paste Special Dialogue box. Here, in the operation section, you should select Multiply and tap on Ok button. For more info, click on office.com/setup.

4. Remove Blank Cells:

If you have a huge Excel file, then you should select blank cells at a time. For this, you should select the data set in Excel. Then, Go To dialogue box, and press F5 key. Now, you should Go To Special dialogue box, and then select the Special option. Here in Go To Special, you should select Blanks and then tap on OK button.

5.  Find And Replace Feature:

To replace data in Excel Spreadsheet, you should open the Find and Replace dialogue box, and then press Ctrl + H. Now, you should tap on Replace. Then in the Find what: box, you should enter the data which you want to remove. Here, in Replace with box, you need to enter the data which you want to replace it. Remember, you can replace particular data by clicking on Replace All option.

6. Remove Formatting In Excel:

In this, first you have to select your data set of Excel. Then, you should hit on the Home tab. After this, you should select Clear option. At last, you have to select Clear Formats.

The above method helps to clean data in Excel. If the user need more detail about Excel, visit to the Microsoft via www.office.com/setup.

Amazing Tips For Creating A Stylish Excel Spreadsheet

Many users use Excel application as it is easy to use but if the user has to create a stylish Excel spreadsheet then this is a very tough job. You can setup MS Office software in your device via www.office.com/setup. If you create a beautiful spreadsheet then it will be pleasing to your eyes and also makes bugs more clear. Hence, in this blog you will read how to create a stylish and beautiful spreadsheet.

How To Create a Stylish Excel Spreadsheet? Office.com/setup

1. Check out a Template:

If you don’t know where to start, then you should check out the templates which are available on the web. Just look at them.

2. Select the Right Font:

You should always choose a clear and a readable font like Arial or Calibri for creating an attractive and professional like spreadsheet.

3. Limit Use of Different Fonts:

Always keep in mind that two different types of font styles will help to differentiate between headers and text. But if there are many fonts then it will not look like professional.

4. Center your Title:

To create a professional spreadsheet, you should center your title for your spreadsheet. For this, you should use the “Merge Cells” or “Center Across Selection” options.

5. Start in b2:

To create some space in your document, you should leave row 1 and column A blank so that spreadsheet look neat and tidy and also easy to understand.

6. Bold your Headers:

As Headers is different from the main text, so you should bold them and this will make your spreadsheet easier to read.

7. Vary your Font Sizes:

For making spreadsheet easy to read, you should use a larger font size for headings and sub-headings. Proper font size will make your spreadsheet clear and easy to understand.

8. Align Text:

You should format your cells and also select the options which allow you to align your text according to your needs. Like you should text aligns to the left by default, and numbers align to the right and give them headings according to the table contents.

9. Create Space:

You can adjust the height and width of cells just to make your spreadsheet neat and tidy.

10. Leave Some Cells Empty:

If in case, your spreadsheet looks complex, then you should leave a row or column empty to maintain the breaks in the data. This will improve readability.

11. Use Color Sparingly:

You can use colors in Excel just to highlight the key rows or columns, to make your spreadsheet more attractive and easy to read. But remember, don’t overdo it.

12. Keep Dark Text on a Light Background:

If you are choosing colors for your spreadsheet, then you should select dark text on a light background, as this will make your spreadsheet easier to read and prints better.

 13. Add an Image:

In your spreadsheet, you can add a simple image like your company logo as this will make your spreadsheet professional and attractive.

14. Name your Worksheets:

You should name your worksheet as this will help to keep everything well-organized and in a professional way.

The above are some of the tips which help to create a stylish spreadsheet. For more help or support, you can contact the expert of MS Office through Office.com/setup.

What are the Ways To Manage Excel Workbook Connections?

Some of the computer users, don’t know how to manage the Excel Workbook connection. So, in this blog you will read the ways to manage Excel Workbook connection. To know more about Excel, go to the site of Microsoft Office via www.office.com/setup.

Method To Manage Excel Workbook Connections: Office.com/setup

1: Using Workbook Connections Dialog Box:

It helps to manage the single or multiple connections with the external data sources of your workbook. Along with this, it helps to edit, refresh, create and also delete connections which are there in workbook. It also helps to show the location of each connection which is used in the current Excel workbook. With this, you can diagnose the error message related to external data connections. It also helps to redirect connection to different data or to the different server. Or you can also replace the connection file with the existing connection. With this, you can make & share connection files.

To Manage Excel Workbook Connection By Using Workbook Connections Dialog Box:

  1. Identify a Connection: You can identify all the connections in the Excel workbook as it appears automatically with the following information like Name of the connection with connection Properties, description about the connection, and it also gives the information when was the connection was last refreshed. If the refresh option is blank, this means the connection has not refreshed yet.
  2. Add a Connection: You should click on the Add option just to get the dialog box of Existing Connections.
  3. Display Connection Information: First, you have to select a connection from the opened Existing Connections dialog box. Then, you should click on the Properties option and then the Connection Properties dialog box will open.
  4. Refresh The External Data: First, you have to click on the arrow option which is next to the Refresh option. After this, you should perform anyone of the following:

    If in case, you wish to refresh any specific connections, then you should make selections of those connections. After this, click on the Refresh option.

    If you want to refresh all the connections of your workbook, then you should clear off all the connections. Then, hit on the Refresh All option.

    For getting, the status information about refresh operation then you should select the connections for which you require to extract information. Then, press Refresh Status option.

    To stop the current running refresh operation, hit on the Cancel Refresh option.
  5. Remove One Or More Connections: You should select the connections which you wants to remove from your workbook. Then, hit on the Remove option.

2: By Using Refresh External Data Connection Option:

If you want to Use Refresh External Data Connection Option, first you should click on any cell of your Excel table which has the connection. After this, you should perform any of the following operation:

Automatically refresh data when excel workbook is opened

Automatically refresh data at regular interval

To Automatically Refresh Data When Excel Workbook Is Opened, you should click on the cell which is in the external data range. Then on the Data tab, you should go to the Queries & Connections. After this, click on the arrow key in the Refresh All option, and then click on the Connection Properties. Here, from the opened dialog box of Connection Properties, you should click on the Usage tab, in Refresh control. Then, just select the check box “Refresh data when opening the file”. To save your workbook, you should select the check box “Remove data from the external data range before saving the workbook”.

To Automatically refresh data at regular interval, you should click on the cell which is there in the external data range. Then, on the Data tab, you should go to the Queries & Connections. After this, click on the arrow key in the Refresh All option, and then click on the Connection Properties. Now, in the opened Connection Properties dialog box, you should click on the Usage tab option. At last, you should select the check box Refresh every. After this, you should set the minute interval which will automatically refresh your data.

3: By Creating Office Data Connection (ODC) File:

You can use the Data Connection Wizard or Connection Properties dialog box to create an Office Data Connection (ODC) file (.odc). For this, you should make new connection with data source. Like Connect to SQL Server Analysis Services Database, Import or export text files and also you can move data from Excel to Access. Here, you can also make use of the existing connection. After this, you have to save the connection detail in the connection file. You should click on the Export Connection File option which is on the Definition tab of Connection Properties dialog box. It will open the File Save dialog box, you should save your current connection information in the ODC file.

For details information about Excel application, visit to the official site of MS Office via www.office.com/setup.

How You Can Transfer Excel Contacts to IPhone Manually?

How You Can Transfer Excel Contacts to IPhone Manually? Office.com/setup

If the user wants to access their Excel contacts to IPhone, then you should read this blog. But if you want any kind of support then just contact to the expert of MS Office via www.office.com/setup.

Method to Transfer Excel Contacts To IPhone Manually: office.com/setup

Method 1. Import Excel Contacts To iPhone by Using iTunes:

For this, you should export contacts from excel to CSV format. Then, import CSV contacts into the Outlook application. After this, just synchronizing those contacts from Outlook app to iPhone by using iTunes.

1.Export Contacts From Excel:

First, from the menu you should click on the “File” option and then select “Save As” option. After this, click on the drop-down menu of “Save as type” and then select “CSV (Comma delimited)” format file type. Now, you should name the CSV file and tap on “Save” button. Here, you will get the confirmation box which prompts, you want to use the CSV file or not. Then, just hit the “Yes” button. You must remember the location of this CSV file as it will use it again when you import the contacts to outlook.

2. Import Contacts to Outlook:

You should open your outlook program. Then, follow this path: “File > Open & Export > Import/Export > Import From Another Program or File > Next”. After this, click on the “Comma Separated Values” from the list of various file formats and then click on the Next. Here in the opened screen, you should search for the CSV file which you have exported and then just double-tap on it. Just ignore the duplicate contacts and click on the Next option. After this, choose the folder where you want to import Excel contacts. Now, you should import Excel contacts in the already existing folder and click on the “Next” option. At the end, you should put a checkmark next to the name of the CSV file and then click on the “Map Custom Fields” option. At the end, you should tap on the finish button just to import Excel contacts into Outlook.

3. Sync With iPhone Via iTunes:

You should connect your iPhone with your PC just by using the USB cable. Then, you should start the iTunes app on your PC. After this, you should click on the iPhone device name which is there on the right side of the iTunes app. Then, you have to click on the “Info” tab from the horizontal menu bar. After this, you should put a checkmark next to the check box of “Sync Contacts With”. Here, you should select your email account in the Outlook to which you want to import your Excel contacts. Then, you have to click on the apply button just for transferring up the contacts to the iPhone. At this point, you should click on the “Sync” button according to the status of how the iTunes and iPhone are configured.

Method 2. Import Excel Contact To iPhone by Using iCloud:

For this, you should convert your Excel file into VCF format because the iCloud Application import the only file of VCF format.

 1: You should convert your XLSX file in the proper format. Then, you have to save your Excel workbook in the CSV format. After this, press the Win+R key and in the opened Run window you should type the word ‘Run’ and tap on Enter button. Here from the Contacts windows navigation bar, you should select the “Import” option. Now, you should choose the option for Comma Separated Value Field or CSV. Then, you have to browse on the folder path where the .csv file is saved. Just choose the CSV file and click on the Next option. After this, Map the appropriate fields and tap on the Finish button. At the end, select the Export option as vCard and then click on the Export button.

 2: First, you should log in to the iCloud.com page just by using your Apple ID & Password. Then, you have to click on the Contacts icon just to import contacts of the Excel spreadsheet into iPhone. Now, click on  the Gear Icon and then choose the Import vCard option. Here, from the folder path in which the VCF file is saved just click on the Open button. You will see all contacts will get uploaded. Then, in the iPhone Device you should open the Contacts Section. When the iCloud accounts synchronize with your iPhone device then you can see the converted contacts from Excel spreadsheet to iPhone.

For details, you should visit to the site of MS Office via www.office.com/setup.

If Data Lost In Excel! How You Can Recover it?

Sometimes when you are working on Excel Application, your data get lost or disappear. In this situation, the user gets worried and irritated. So, in this blog you will read how you can get lost data in Excel application easily. But if the user of MS Office needs assistance, then they can anytime contact to the customer care of MS Office via www.office.com/setup.

Reasons of Excel Spreadsheet Data Loss : office.com/setup

The reason of Excel Spreadsheet data gets disappear are format conversion, unsaved work, in case of cell formatting, and also when cells are merging.

Steps To Recover Lost Data in Excel:

  1. Recover With Restore Previous Version Option: For this, first open the File Explorer on your computer and then just search for your Excel file. Now, you have to choose your lost data Excel file and just right-click over it. Then, from the list of options you have to select “Properties” option. Here, you have to click on the “Previous Versions” tab and then you should select the preferred version in which you want to get back to your Excel file. At the end, you have to click on the “Restore” button. With these steps, you can restore lost Excel file.
  2. Use Excel Repair Option: You should restart your computer system before you start the repair functions. Then, you should go to Control Panel and just click on Programs and Settings option. Here, you should find Office and then right-click on it. After this, from the options you should click on Change. Now, you will view a Repair option appears on your screen and then just click on Repair. At last, you should open the MS Excel file just to check data is fixed or not.
  3. Recover With Excel “Recent Option”: First, you have to launch MS-Excel and then you should select the “file” option from the top. After this, you should press on “open”. Now, you should select the “recent” option and then just select on recover unsaved workbook.
  4. Try Microsoft Office Application Recovery Option: For this, you have to choose All Programs after clicking on the Start option. Now, you should select Microsoft Office. After this, you have to choose Microsoft Office Tools. Then, you have to select Microsoft Office Application Recovery. Here, you have to choose Microsoft Office Excel in the resulting dialog box. At this point, you should click on Recover Application. Note, this procedure will take some time to complete. At last, you have to respond to the Send Report To Microsoft prompt. Here, this tool will quit the Excel and after this, it will allow your Excel application to restart with a list of recoverable workbook.

Read More – How To Troubleshoot if “Photoshop Unable to Create New Files or Open Existing Ones?

The above method will help you to recover the lost data in Excel application. But if you need any help or support, then you can anytime contact to the customer care of MS Office through www.office.com/setup.  The experts of Microsoft are available for 24/7 for its customer and try their level best to solve the query of the user.