professorexcel

[professorexcel]

Paste to Visible Cells Only: How to Skip Filtered Out Cells

You have copied a cell and want to paste it to multiple other cells. Sounds easy, right? But what if you only want to paste this value to all visible cells? So that you skip all filtered out or hidden cells in-between? It’s also very easy but requires one more small step. Here is how to do that!

Method 1: Use “Go to special” to paste to all visible cells

This first method relies on basic Excel features. It uses the Go to special function. Here are the steps in detail.

Paste to visible cells only: The key is to select the visible cells with the Go To Special function first.

Copy the cell you want to paste to all the visible cells.

Select the whole cell range you want to paste it to, including the hidden or filtered out cells.

Open the Go to special window. In order to do that, click on the small arrow next to the “Find & Select” button. It’s located on the right-hand side of the Home ribbon in Excel. Now, click on “Go To Special”. Alternatively, you can use keyboard shortcuts: Press Ctrl + G on the keyboard to open the normal “Go To” window. Now, either click on the “Special” button or press Alt + S on the keyboard.

In the Go To Special window, select “Visible cells only” and confirm with OK.

Now, you can see that only the visible cells are selected. Paste your copied cells now as you would normally do.

Method 2: Do it faster with Professor Excel Tools

You can paste to visible cells only faster with Professor Excel Tools.

Paste to visible cells with Professor Excel Tools.

Like in our method 1 above, select the whole cell range first.

On the Professor Excel ribbon, click on the arrow of “Selection Tools” and then on “Select all visible cells”.

Paste your values.

You can try Professor Excel Tools for free. This add-in extends Excel with more than 125 new features, all on a new “Professor Excel” ribbon. Click here to start the download (no sign-up needed).

This function is included in our Excel Add-In ‘Professor Excel Tools’

Learn more

Download Free Trial

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Image by SplitShire from Pixabay

Der Beitrag Paste to Visible Cells Only: How to Skip Filtered Out Cells erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/3GEd1rP

[professorexcel]

How to Fill Down Empty Cells With Values From Above

You work with a table or PivotTable and have an issue: Only the first item is filled. The cells underneath are blank, indicating that the first cell should repeat. Here is how to easily fill down the first filled cell for all blank cells underneath.

Example: Fill down empty cells with values from above

Goal: Fill down the name from the cells above.

Let’s take a look at the simple example first. In the following sections, we explore then the solutions.

In our example, you have sales persons in the first column. The problem: The name is only written in the first row. The following 11 cells underneath are empty. You want to fill down the name from the first cell as indicated with the arrows.

Manually fill down values from above

You could of course just use copy and paste to copy the values down. But what, if you deal with hundreds or thousands of rows?

There is a better way.

Go to any cell outside your table. Write = and refer to the cell above. So, in cell F4, write =F3. Press enter and copy the cell by pressing Ctrl + C on the keyboard.

Select the cells in the first column, no matter if there are values or if they are blank. Select them down until you reach the last row in your table.

Open the Go to special window: On the Home ribbon, click on Find & Select and then on “Go to special”. Alternatively, press Ctrl + G on the keyboard and then on “Special”. Select “Blanks” and click on OK.

Now, only the empty cells are selected.

Because the original formula in cell F4 is still copied, you can simply paste it now: Press Ctrl + V on the keyboard.

This last step is optional: You can now convert the pasted formulas to values. In order to do that copy the whole first column and open the Paste Special window (press Ctrl + Alt + V on the keyboard). Select Values and confirm with OK.

Shortcut: Speed up the process to fill down values in empty cell

Our Excel add-in Professor Excel Tools can speed up this process significantly: As you can see in the following animation, it only takes 9 seconds.

Fill down all empty cell with the values from above with Professor Excel Tools.

Again, type =F3 into cell F4 (or similar in any other cell outside your table to fill). Copy cell F4.

Select all cells in the column. Then, select all blank cells by clicking on “Select all blank cells” from the “Selection Tools” on the Professor Excel ribbon.

Paste by pressing Ctrl + V on the keyboard.

If you like, you can convert all pasted formulas to values. Just click on “Change reference” on the Professor Excel ribbon and then on “Values”.

This function is included in our Excel Add-In ‘Professor Excel Tools’

Learn more

Download Free Trial

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Special case: PivotTables

With PivotTables, it’s even easier to fill all blank cells down.

Option 1: Fill down all columns at once in a PivotTable

Fill down cells in PivotTables by selecting the option to “Repeat all Item Labels”.

Select any cell within your PivotTable.

Go to the Design ribbon, click on the small arrow in the corner of the Report Layout button and then on “Repeat all item labels”.

Option 2: Repeat items in one column only

You can alternatively only repeat item labels for one column in a PivotTable.

Right click on a cell within the column you want to repeat the cell above.

Click on Field Settings.

Go to the Layout & Print tab and set the checkmark at “Repeat item labels”.

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Image by Son Hoa Nguyen from Pixabay

Der Beitrag How to Fill Down Empty Cells With Values From Above erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/3lWHCc6

[professorexcel]

Bulk Trace Dependents & Precedents: How to Add Arrows for Many Cells!

Excel has a very useful feature when it comes to formula auditing: It allows you to insert arrows to all preceding or depending cells. The problem: You can only apply it for one cell at once. But what, if you want to audit a larger range of cells? Here is how to bulk insert trace dependents and precedents arrows at once.

Method 1: Insert arrows manually cell by cell

At first, let’s take a quick look at how to insert the arrows for one cell only. If you know how to do that please proceed to the next method below.

Trace dependents and precedents in Excel.

For inserting one arrow at a time, select your desired cell. Then click on “Trace Precedents” or “Trace Dependents” on the Formula ribbon. Now, you can see the arrows.

If you want to clear the arrows, click on Remove Arrows.

Method 2: Bulk insert trace dependents and precedents arrows for many cells

The most convenient method: Use Professor Excel Tools. This Excel add-in comes with an entirely new ribbon full of powerful features.

Two of the features are bulk tracing depending and preceding cells for many cells at the same time.

Use Professor Excel Tools to bulk trace dependents and precedents for many cells.

Select all the cells you would like to trace.

Click on either “Trace Precedents” or “Trace Dependents” on the Professor Excel ribbon (highlighted in yellow on the screenshot above).

You can try it for free. Click here and the download starts right away.

This function is included in our Excel Add-In ‘Professor Excel Tools’

Learn more

Download Free Trial

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Method 3: Use a VBA macro to bulk trace dependents and precedents

Another option to bulk trace precedents and dependents is via a short VBA macro.

Open the VBA editor, insert a new module, copy and paste one of the following code snippets and press start. Too fast? Here is a short tutorial with more pictures.

Code for showing all precedents:

Sub showAllPrecedents() Dim cell As Range For Each cell In Selection cell.ShowPrecedents Next cell End Sub

Code for showing all depending cells:

Sub showAllDependents() Dim cell As Range For Each cell In Selection cell.ShowDependents Next cell End Sub

Further reading

I have written a larger guide about tracing depending and preceding cells. Just follow this link to learn more.

Image by Paul Barlow from Pixabay

Der Beitrag Bulk Trace Dependents & Precedents: How to Add Arrows for Many Cells! erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/3lOLbB9

[professorexcel]

Why Is My Excel File So Big? Here Is How to Find It Out!

You have saved your Excel file and it is suddenly very big? Dozens of Megabyte although it was supposed to be rather small? In this article you learn how to find out what is causing large file sizes of Excel workbooks and how to fix it.

Check which sheet causes the large file size!

Method 1: Conveniently check with an Excel add-in why the file is very big

The fastest method of checking which worksheet causes the workbook to be very big is with Professor Excel Tools.

Check the file size of each worksheet with Professor Excel Tools.

The overview lists all worksheets with their respective file sizes.

Open the “Reduce File” feature on the Professor Excel ribbon.

Click on “Check size of each sheet”. Lean back until the checking is done. You can now see for all worksheets, how many Kilobyte they cause.

The next step would be to reduce the size of these worksheets. Professor Excel Tools can next try the reduce the size for the two most common problems: Unnecessary formatting and pictures.

This function is included in our Excel Add-In ‘Professor Excel Tools’

Learn more

Download Free Trial

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Method 2: Copy sheets or groups of sheets to new file

Copy sheets to a new workbook to check the file size.

If you don’t use Professor Excel Tools, you could still do the same steps manually. Copy each worksheet into a new workbook and check the file size:

Right-click on the sheet name on the bottom of the screen.

Click on “Move or Copy…”

Select “(new book)” and set the checkmark at “Create a copy”.

Save the new workbook on your hard drive and check the size.

You can also speed that up by copying groups of worksheets into a new workbook.

Possible reasons, why the Excel file is big

After you have figured out, which worksheet causes large workbook sizes, you should look for the most common reasons. These are:

Reason 1: Formatting needs a lot of disk space

Description: In some cases, when you apply formatting to an entire column or row, Excel needs a lot of space. Usually, Excel is smart enough not to save the formatting for each cell separately, but sometimes it doesn’t work.

Solution: Clear all formatting below and to the right of the cells with content.

Our Excel add-in Professor Excel Tools can help you with that.

Reason 2: Pictures and other object increase file size

Description: Images can increase your Excel file significantly. It depends of the format, size, compression and number of pictures, but this problem is relatively simple to solve.

Solution: You could either delete or compress the images in your Excel file.

For deleting all pictures, first select the pictures and then press Del on the keyboard. If you want to delete all pictures in the whole workbook, you can use Professor Excel Tools: Click on Reduce File on the Professor Excel ribbon and then set the checkmarks at “Reduce size of pictures” and “Delete all pictures”.

For compressing pictures, select one image in your workbook. Then activate the Format ribbon which is now available (Mac: “Picture Format”). Click on “Compress Pictures” so that a new window opens for defining the compression settings. Follow the steps on the screen.

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Reason 3: A lot of data can cause big Excel files

Description: A lot of data can also increase the file size. This one is actually tricky to solve.

Solution: Are you sure that you need all the (raw) data? Is there duplicate data? Is there data on hidden or very hidden sheets. Whenever you don’t necessarily need the data for your workbook to function, you can consider deleting it. Also splitting the workbook in separate files (one for importing data, one for data manipulation, etc.) could be a solution.

More reasons

For more reasons for large Excel files and the fixes, please refer to this article. In this article, I have also measured the potential reduction.

Image by Sasin Tipchai from Pixabay

Der Beitrag Why Is My Excel File So Big? Here Is How to Find It Out! erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/3y9lMHb

[professorexcel]

Follow Up INDIRECT: How to Easily Evaluate INDIRECT Functions

INDIRECT functions are often a bit complicated to follow up. They can be useful, especially for referring to different sheets but not easily to evaluate. Here are three methods to follow up INDIRECT functions in Excel.

Method 1: Use the INDIRECT manager to follow up INDIRECT function

The first method is most convenient. Our Excel add-in Professor Excel Tools has a built-in INDIRECT manager. Just select the cell you want to evaluate and click on the small arrow next to “Follow Precedents” on the Professor Excel ribbon (highlighted in yellow below).

Easily follow up INDIRECT functions with Professor Excel Tools.

Then, click on INDIRECT Manager and you will see the window like in the screenshot above. For every INDIRECT function in the currently selected cell you will see the original function as well as the evaluated result. You can then select one of the functions and click on “Go to selected cells”.

You can try Professor Excel Tools for free. It provides more than 125 features for every professional working with Excel. Just click here and the download starts right away.

This function is included in our Excel Add-In ‘Professor Excel Tools’

Learn more

Download Free Trial

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Method 2: Convert reference to value by pressing F9

The second method is quite simple and works not only with INDIRECT, but all formulas and functions in Excel.

Press F9 on the keyboard to see the values of the INDIRECT function.

Select a cell with an INDIRECT function.

Press F2 on the keyboard to enter the cell.

Select the arguments of the INDIRECT function with the mouse (or keyboard, if you prefer).

Press F9 on the keyboard. Excel now converts the INDIRECT function to the final cell reference.

Press Esc on the keyboard to leave the cell and discard the conversion to values.

Method 3: Use the evaluate Formula feature in Excel to follow up INDIRECT function

The last method is the Evaluate Formulas feature in Excel. This method also works for all kinds of formulas.

Follow up INDIRECT function with the “Evaluate Formula” feature in Excel

Like in all the other methods above, select the cell first (here: C19).

Click on Evaluate Formula on the Formulas ribbon.

Now you can navigate through the formula by clicking “Evaluate”

Image by LoggaWiggler from Pixabay

Der Beitrag Follow Up INDIRECT: How to Easily Evaluate INDIRECT Functions erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/3pGs1OS

[professorexcel]

Merge Excel Files: How to Combine Workbooks into One File

You have several Excel workbooks and you want to merge them into one file? This could be a troublesome and long process. But there are 6 different methods of how to merge existing workbooks and worksheets into one file. Depending on the size and number of workbooks, at least one of these methods should be helpful for you. Let’s take a look at them.

Summary

If you want to merge just a small amount of files, go with methods 1 or method 2 below. For anything else, please take a look at the methods 4 to 6: Either use a VBA macro, conveniently use an Excel-add-in or use PowerQuery (PowerQuery only possible if the sheets to merge have exactly the same structure).

Method 1: Copy the cell ranges

Copy and paste the source content into your main workbook.

The obvious method: Select the source cell range, copy and paste them into your main workbook. The disadvantage: This method is very troublesome if you have to deal with several worksheets or cell ranges. On the other hand: For just a few ranges it’s probably the fastest way.

Method 2: Manually copy worksheets

Copy worksheets separately to the “master” workbook.

The next method is to copy or move one or several Excel sheets manually to another file. Therefore, open both Excel workbooks: The file containing the worksheets which you want to merge (the source workbook) and the new one, which should comprise all the worksheets from the separate files.

Select the worksheets in your source workbooks which you want to copy. If there are several sheets within one file, hold the Ctrl key and click on each sheet tab. Alternatively, go to the first worksheet you want to copy, hold the Shift key  and click on the last worksheet. That way, all worksheets in between will be selected as well.

Once all worksheets are selected, right click on any of the selected worksheets.

Click on “Move or Copy”.

Select the target workbook.

Set the tick at “Create a copy”. That way, the original worksheets remain in the original workbook and a copy will be created.

Confirm with OK.

One small tip at this point: You can just drag and drop worksheets from one to another Excel file. Even better: If you press and hold the Ctrl-Key when you drag and drop the worksheets, you create copies.

Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Learn more

Download Free Trial

Add more than 120 great features to Excel!

Method 3: Use the INDIRECT formula

The next method comes with some disadvantages and is a little bit more complicated. It works, if your files are in a systematic file order and just want to import some certain values. You build your file and cell reference with the INDIRECT formula. That way, the original files remain and the INDIRECT formula only looks up the values within these files. If you delete the files, you’ll receive #REF! errors.

With the INDIRECT formula you can link to other files. Only condition: the source file must be open in the background.

Let’s take a closer look at how to build the formula. The INDIRECT formula has only one argument: The link to another cell which can also be located within another workbook.

Copy the first source cell.

Paste it into your main file using paste special (Ctrl + Alt + v ). Instead of pasting it normally, click on “Link” in the bottom left corner of the Paste Special window. That way, you extract the complete path. In our case, we have the following link: =[160615_Examples.xlsm]Thousands!$C$4

Now we wrap the INDIRECT formula around this path. Furthermore, we separate it into file name, sheet name and cell reference. That way, we can later on just change one of these references, for instance for different versions of the same file. The complete formula looks like this (please also see the image above): =INDIRECT(“‘”&$A3&$B3&”‘!”&D$2&$C3)

Important – please note: This function only works if the source workbooks are open.

Method 4: Merge files with a simple VBA macro

You are not afraid of using a simple VBA macro? Then let’s insert a new VBA module:

Go to the Developer ribbon. If you can’t see the Developer ribbon, right click on any ribbon and then click on “Customize the Ribbon…”. On the right hand side, set the tick at “Developer”.

Click on Visual Basic on the left side of the Developer ribbon.

Right click on your workbook name and click on Insert –> Module.

Copy and paste the following code into the new VBA module. Position the cursor within the code and click start (the green triangle) on the top. That’s it!

Sub mergeFiles() 'Merges all files in a folder to a main file. 'Define variables: Dim numberOfFilesChosen, i As Integer Dim tempFileDialog As fileDialog Dim mainWorkbook, sourceWorkbook As Workbook Dim tempWorkSheet As Worksheet Set mainWorkbook = Application.ActiveWorkbook Set tempFileDialog = Application.fileDialog(msoFileDialogFilePicker) 'Allow the user to select multiple workbooks tempFileDialog.AllowMultiSelect = True numberOfFilesChosen = tempFileDialog.Show 'Loop through all selected workbooks For i = 1 To tempFileDialog.SelectedItems.Count 'Open each workbook Workbooks.Open tempFileDialog.SelectedItems(i) Set sourceWorkbook = ActiveWorkbook 'Copy each worksheet to the end of the main workbook For Each tempWorkSheet In sourceWorkbook.Worksheets tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count) Next tempWorkSheet 'Close the source workbook sourceWorkbook.Close Next i End Sub

Method 5: Automatically merge workbooks

The fifth way is probably most convenient:

Use Professor Excel Tools to combine all Excel files.

Click on “Merge Files” on the Professor Excel ribbon.

Now select all the files and worksheets you want to merge and start with “OK”.

This procedure works well also for many files at the same time and is self-explanatory. Even better: Besides XLSX files, you can also combine XLS, XLSB, XLSM, CSV, TXT and ODS files.

To do that you need a third party add-in, for example our popular “Professor Excel Tools” (click here to start the download).

Here is the whole process in detail:

Just click on “Merge Files” on the Professor Excel ribbon, select your files and click on OK.

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Method 6: Use the Get & Transform tools (PowerQuery)

The current version of Excel 365 offers the “Get & Transform” tools to import data. These functions are very powerful and are supposed to replace the old “Text Import Wizard”. However, they have one useful feature: Import a complete folder of documents.

The requirements: The workbooks and worksheets you want to import have to be in the same format.

Please follow these steps for importing a complete folder of Excel files.

Create a folder with all the documents you want to import.

Usually it’s the fastest to just copy the folder path directly from the Windows Explorer. You still have the change to later-on select the folder, though.

Within Excel, go to the Data ribbon and click on “Get Data”, “From File” and then on “From Folder”.

Paste the previously copied path or select it via the “Browse” function. Continue with “OK”.

If all files are shown in the following window, either click on “Combine” (and then on “Combine & Load To”) or on “Edit”. If you click on “Edit”, you can still filter the list and only import a selection of the files in the list. Recommendation: Put only the necessary files into your import folder from the beginning so that you don’t have to navigate through the complex “Edit” process.

Next, Excel shows an example of the data based on the first file. If everything seems fine, click on OK. If your files have several sheets, just select the one you want to import, in this example “Sheet1”. Click on “OK”.

That’s it, Excel now imports the data and inserts a new column containing the file name.

For more information about the Get & Transform tools please refer to this article.

Next step: Merge multiple worksheets to one combined sheet

After you have combined many Excel workbooks into one file, usually the next step is this: Merge all the imported sheets into one worksheet.

Because this is a whole different topic by itself, please refer to this article.

Image by MartinHolzer from Pixabay

Der Beitrag Merge Excel Files: How to Combine Workbooks into One File erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/2Phpjws

[professorexcel]

Greek Letters: How to Insert Alpha, Delta, Omicron in Excel

In Mathematics, many Greek letters have meanings. For example, the delta symbol, or the sigma character. Also, with nowadays naming the COVID-19 variants after Greek letters, you might need to use them in Excel. Here are 5 simple methods of how to add them to Excel cells and to use them in Excel charts – in Windows and Mac!

Method 1: Use the Symbol window to insert Greek letters

The “traditional” way to insert the sign showing the difference in Windows is through the Insert Symbol window:

Insert the Delta Sign in Windows via the Insert Symbol window.

On the Insert ribbon, click “Symbol” (the right-most button).

In the Symbol window, select “Unicode (hex)” in the from field.

Type the number of your desired Greek letter into the “Character code” field (here 0394 for delta Δ). You should now see the Greek character selected above.

Click on Insert to insert the symbol into the cell or chart.

This table has all the values you type into the “Character code” field.

LetterSmall Greek letterCharacter code for small letterCapital Greek letter Character code value for capital letter Alphaα03B1Α0391Betaβ03B2Β0392Gammaγ03B3Γ0393Deltaδ03B4Δ0394Epsilonε03B5Ε0395Zetaζ03B6Ζ0396Etaη03B7Η0397Thetaθ03B8Θ0398Iotaι03B9Ι0399Kappaκ03BAΚ039ALa(m)bdaλ03BBΛ039BMuμ03BCΜ039CNuν03BEΝ039DXiξ03BFΞ039EOmicronο03C0Ο039FPiπ03C1Π03A0Rhoρ03C2Ρ03A1Sigmaσ03C3Σ03A3Tauτ03C4Τ03A4Upsilonυ03C5Υ03A5Phiφ03C6Φ03A6Chiχ03C7Χ03A7Psiψ03C8Ψ03A8Omegaω03C9Ω03A9

Table with all the character codes of Greek letters that you need for the “Symbol” window.

Method 2: Use Professor Excel Tools to insert Greek characters

The second method works similar to the first one: You select the Greek letter you want to insert from a list. But this time, we use Professor Excel Tools, our Excel add-in to boost your productivity.

Use Professor Excel Tools to insert Greek letters easily.

On the Professor Excel ribbon, click on “Insert Symbol”.

Select “Greek Characters” and click on the letter you want to insert.

Click on “Insert”.

The advantages of this method: Selecting the category of Greek characters is very fast and you can bulk insert the characters to all selected cells.

Please download the free trial version of Professor Excel Tools here.

This function is included in our Excel Add-In ‘Professor Excel Tools’

Learn more

Download Free Trial

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Method 3: Use the UNICHAR function in Excel

The third method is using the built-in UNICHAR function. Just type

=UNICHAR(927)

into an Excel cell and it returns the Omicron letter. Please refer to the following table for all the other unicode numbers of Greek characters.

This method also works under MacOS.

Letter Small Greek letter UNICHAR small letter Capital Greek letterUNICHAR Capital LetterAlphaα945Α913Betaβ946Β914Gammaγ947Γ915Deltaδ948Δ916Epsilonε949Ε917Zetaζ950Ζ918Etaη951Η919Thetaθ952Θ920Iotaι953Ι921Kappaκ954Κ922La(m)bdaλ955Λ923Muμ956Μ924Nuν957Ν925Xiξ958Ξ926Omicronο959Ο927Piπ960Π928Rhoρ961Ρ929Sigmaσ963Σ931Tauτ964Τ932Upsilonυ965Υ933Phiφ966Φ934Chiχ967Χ935Psiψ968Ψ936Omegaω969Ω937

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Method 4: Copy & paste

Probably not as beautiful, but it usually works: Alternatively, just copy and paste the Greek letters from one of the tables above.

This method also works under MacOS

Method 5: Insert Greek letters in MacOS

As said already, the method 3 and four above also work under MacOS.

This method

It’s almost the same in Mac OS as in Windows, but the window looks a bit different. Still, you have to go to the Insert ribbon and click on Symbol on the right-hand side:

Insert the delta symbol on a Mac with the Character Viewer (very similar to Windows).

On the Insert ribbon, click on Symbol (on the right-hand side).

In the Character Viewer, go to Mathematical Symbols.

Insert the ∆ by double-clicking on it.

Add the Greek characters to a chart

You want to show Greek characters on a chart? It works the same way as described above. The only difference: Insert a text field first.

Before you can insert a Greek letter on a chart, insert a Text Box on it.

In order to do this, select your chart. Then go to the Insert ribbon. On the right-hand side, click on the button “Text Box” and draw it onto the chart. Now, you can simply copy & paste or insert the Greek letter there.

IImage by PImage by Michelle Raponi from Pixabay

Der Beitrag Greek Letters: How to Insert Alpha, Delta, Omicron in Excel erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/3xGxnxh

[professorexcel]

Per Mille ‰: How to Show Excel Values per Mille

Working with percent values in Excel is very easy and convenient. In some cases, however, you want to work with per mille values. My impression: It was not really considered when Excel was developed. In this article, you will find three main methods, each with different options to insert per mil (or: parts per thousand).

Method 1 (fastest): Insert the per mille symbol ‰ into the cell next to your value

Our first method might not be as elegant, but it’s fast: You insert the per mille sign into a cell next to your value.

Please note: Your value, calculated by numerator divided by denominator, must be multiplied by 1,000.

1a) Use Professor Excel Tools

The fastest way to insert the per mille symbol: Use Professor Excel Tools.

The fastest way to insert the per mille symbol: Use our Excel add-in Professor Excel Tools. As you can see, it only takes two quick clicks.

You can try it for free (no sign-up required). Download Professor Excel Tools here or learn more about the Excel add-in.

1b) Copy & Paste per mille sign from here

You don’t have Professor Excel Tools? No problem, here is how to insert the per mille symbol manually. The second fastest way is probably to copy & paste the ‰ sign from here:

‰

1c) Insert the UNICHAR character with the Symbol function

As the last of the three options to insert the per mille sign, you can use the Insert Symbol function in Excel. The first time, it requires a few more steps but since then the ‰ symbol will be listed in the recently used symbols list.

Insert the per mille sign from the Symbol window.

Go to the Insert ribbon and click on “Symbol” on the right-hand side of the ribbon.

Type “2030” into the “Character code: ” field and make sure that “Unicode (hex)” is selected in the from field.

Click on Insert.

Method 2: Convert value to text and insert ‰ into same cell

The second method will show the per mille sign inside the same cell. Unfortunately, this converts the number to a text value and can therefore not be used in further calculations.

Copy and paste the following Excel formula and replace C4 and C5 by your numerator and denominator:

=C4/C5*1000&"‰"

Do you want to further “fine-tune” the output? Then, use the slightly changed formula.

Overview: Per mille formulas.

Use this formula for no digits:

=TEXT(C4/C5*1000,"0")&"‰"

For one digit, use this formula:

=TEXT(C4/C5*1000,"0.0")&"‰"

Use this formula for two digits:

=TEXT(C4/C5*1000,"0.00")&"‰"

Method 3: Change the number formatting to per mille

The third approach is a combination of the first two methods above. The output is still a numeric value, but the ‰ sign is shown in the same cell through the formatting. To achieve this, two steps are necessary.

Step 1: Multiply by 1000

Multiply by 1,000 first.

In order to display the values in per mille notation, you have to multiply them first by 1,000.

As you can see in the screenshot, it’s as simple as it sounds. So, the formula is

=C4/C5*1000

Step 2: Add ‰ symbol in the number formatting

Use a custom number format to add the per mille sign.

Press Ctrl + 1 on the keyboard so that the Format Cells window opens.

On the Number tab, go to “Custom” on the left pane. Copy and paste one of the following codes.

For no digits, type:

0‰

If you want to display one digit, use this code (this one is shown in the example screenshot):

0.0‰

For two digits, use this code:

0.00‰

Result after multiplying by 1,000 and changing the format to per mille.

That’s it, confirm with OK and you are done. If you want to learn more about the custom number formats, please refer to the big guide.

Download

Please feel free to download all the examples above in one workbook. Click here to start the download.

Image by Photo Mix from Pixabay

Der Beitrag Per Mille ‰: How to Show Excel Values per Mille erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/3xilNsc

[professorexcel]

Harvey Balls: How to Insert Filled Circles ഠ◔◑◕⬤ in Excel

Harvey Balls are circles filled to some level with color. Or, as Wikipedia says, “are round ideograms used for visual communication of qualitative information”. If you want to use them in Excel, you have two options: Make them dynamic with conditional formatting rules or fix them by inserting them as characters. Here is how to it both ways in three different methods.

Overview

In this article, you will learn three methods of how to insert Harvey balls into Excel cells. Please note the following comments:

Method 1 and 2 insert characters (such as normal letters) into cells. This has the advantage that you can format them as normal letters (for example size and color).

Method 3 uses Conditional Formatting rules and adapt to numeric values in cells. You cannot easily color them, but usually they look a tiny bit better by default.

That being said, let’s get started!

Method 1: The fastest method with Professor Excel Tools

Insert Harvey balls with Professor Excel Tools.

This is the fastest method: Go to the Professor Excel ribbon, click on the drop-down arrow of “Insert Symbol” and then on “More Symbols”. Alternatively, just click on the Insert Symbol button.

In the drop-down, select Harvey Balls. Then, click on the Harvey Ball you want to add to a cell and then on “Insert”.

Just download and install the Excel-add-in Professor Excel Tools and see if it works for you.

This function is included in our Excel Add-In ‘Professor Excel Tools’

Learn more

Download Free Trial

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Insert Harvey Balls as Symbols

Method 2a: Use the =UNICHAR() function

Windows (and also macOS) have built-in special characters. Among them are Harvey balls. To access them, you can use the UNICHAR functions. Just type the following function into an Excel cell:

=UNICHAR(9681)

Harvey balls with the UNICHAR function.

Replace the number with one of the following value for the respective ball.

Harvey BallUnicode (hex) numberഠ3360◔9684◑9681◕9685⬤11044

Unicode (hex) numbers for the different types of Harvey balls.

Method 2b: Just copy and paste

Probably not as beautiful, but it usually works: Alternatively, just copy and paste it from here.

ഠ ◔ ◑ ◕ ⬤

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Method 3: Insert Harvey balls with Conditional Formatting rules

The two methods above insert “fixed” Harvey balls: The won’t change no matter what you type into an Excel cell. Do you want automatically adapt them based on a cell value? In such case, please use a conditional formatting rule.

Step 1: Insert Harvey balls with Conditional Formatting

Insert Harvey balls with Conditional Formatting rules.

Select the cells you want to insert the icons to. These should be cells with numerical values.

Click on Conditional Formatting in the middle of the Home ribbon.

Go with the mouse to “Icon Sets”.

Click on Harvey Balls.

Step 2: Don’t show the numeric values

Now, you can already see the “first draft” of the circles. Next, we will finetune them.

Hide the numbers next to the icons..

Go to the Conditional Formatting button on the Home ribbon again.

Click on “Manage Rules”.

Double click on the Harvey ball rule in order to open and edit it.

Usually, you don’t want to show the numbers next to the icons. In order to achieve that set the check mark at “Show Icon Only”.

Click on OK.

Step 3: Fixate the scale so that it does not change with the minimum and maximum value

Result: Harvey balls with conditional formatting rules.

That’s it, it look quite good already (see the screenshot on the right-hand side).

Unfortunately, they have one disadvantage: If you remove one of the “extreme” values (empty or completely filled balls), all other change. The now highest value will then be the filled Harvey ball and the now lowest value the empty one.

Let’s try to fix this. The goal: When you type 1 into a cell, the empty Harvey ball should be shown. 2 should be filled to one quarter, 3 half and number 5 the completely filled Harvey ball.

Open the Edit Formatting Rule window again (see steps 5 to 8 above). Now, set the Values and Types as shown below.

Fix the scale: Otherwise the Harvey balls change their filling based on the highest and lowest values.

Image by Couleur from Pixabay

Der Beitrag Harvey Balls: How to Insert Filled Circles ഠ◔◑◕⬤ in Excel erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/3qTD4Xq

[professorexcel]

Combine Text in Excel: 5 Easy Methods to Concatenate Cells!

Excel offers three distinct functions as well as a fourth way to combine multiple text cells into one cell. There are countless examples in which you might need this: Combine given- and family names or preparing primary keys for multi-conditional lookups. For example, in a VLOOKUP or INDEX/MATCH formula combination. In this article you learn five methods and in the end, you learn how to deal with a large range of cells.

Method 1: &-sign

Example of the &-sign to combine values.

The easiest way is probably to just use the “&”-sign to combine values in Excel. This method has the same disadvantages like the CONCATENATE function from the method 4 below. It can only regard single cells and not ranges of cells. An advantage of this method is that it’s usually easier to follow up the calculation steps.

As you can see in the screenshot above, you can just refer to several cells and combine them with the &-sign. That’s it.

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Method 2: CONCAT function

Example for the CONCAT function.

The CONCAT function has been introduced to Excel with the version 2016. It’s not available on previous versions of Excel. And that’s already the biggest disadvantage. Besides that, this function is very useful.

The CONCAT is the successor oft he CONCATENATE function and has at least one and at maximum 254 arguments. It can handle separate cells as well as cell ranges. It’s even possible to combine single cells with cell ranges, e.g. =CONCAT(B4,C4:D4) .

As you can see in the screenshot above, using the CONCAT function is very easy. Just refer to the cells you’d like to combine.

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Method 3: Insert text to cell without any formula or function

You don’t want to use a formula or function but just add some text into existing cell? I have written a whole article about that.

The fastest way is to use Professor Excel Tools:

Select your original cells and click on the Insert Text button on the Professor Excel ribbon.

Choose, where (at the beginning or end of the existing text) you want to insert the additional text. You can further define, if you want to insert normal text, subscript or superscript.

Click on Start.

Click here to download Professor Excel Tools. For more information about the Excel add-in, please refer to this site.

This function is included in our Excel Add-In ‘Professor Excel Tools’

Learn more

Download Free Trial

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Method 4: CONCATENATE function

Example for the CONCATENATE function.

Unlike the CONCAT function, CONCATENATE is available in older versions of Excel. Microsoft says within the help section of the CONCATENATE function, that the CONCATENATE is replaced by CONCAT. CONCATENATE is only kept in Excel in order to guarantee the compatibility to older versions on Excel and it’s recommended to use CONCAT instead. CONCATENATE works almost the same way like CONCAT with one major difference: It’s not possible to use ranges of cells as references. Only single cells can be combined. The maximum number of arguments and therefore single cell references is 255.

Method 5: TEXTJOIN function to combine cells

Structure of the TEXTJOIN function.

Since Excel 2016 there is another, advanced option to combine text in Excel. The function is called TEXTJOIN. Besides simply putting text together, the formula offers two advanced options:

You can define a separator between each cell you want to combine, for example a comma.

The formula provides the option to automatically skip blank (empty) cells.

The structure of the TEXTJOIN formula is shown in the figure above. The formula has at least three arguments.

Delimiter: The letter or word is added in-between and separates each cell input. A common delimiter is a comma.

Ignore empty: The second argument is either TRUE or FALSE. If set to TRUE or skipped, blank (empty) cells won’t be regarded. If set to FALSE, empty cells will also be regarded and separated by the delimiter.

The TEXTJOIN function requires at least one argument for the cells or text to be combined. This can be a cell range, a single cell or a value. It’s possible to use up to 252 references.

Example of the TEXTJOIN function.

The screenshot of the example on the right-hand side shows an example for the TEXTJOIN formula. The first part is skipped which means that there is no delimiter. The second argument is set to FALSE so that blank cells aren’t ignored. Eventually the last argument refers to the cell range B4 to D4 which contains the cells to be combined. 

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Example: Combine many cells in Excel

Let’s say, you want to combine 1,000 Excel cells into one cell. The good news: You can use all four methods to accomplish this, even in a simple manner. There is one restriction though: One Excel cell can’t contain more than 32,768 characters.

For combining 1,000 cells in Excel, you can use two basic approaches:

Example of how to combine 1,000 cells using the CONCAT formula.

Use method 1 (CONCAT formula) or method 3 (TEXTJOIN formula) above which can regard cell ranges. The solution using the CONCAT formula is shown in Figure 66. As noted before, the only requirement of this method is that you have to use Excel version 2016.

When using the &-sign for combining 1,000 cells, the easiest way is to insert a helper column.

Insert a helper column (or row, depending on how your data is organized) which always combines the current cell with the previous combination of all cells so far. As you can see in the image on the right-hand side, the helper column (here in cell G6) combines cells G5 (which contains the previous combination) with the current cell F6. The solution in cell I3 only links to the last row in the helper column.

Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Learn more

Download Free Trial

Add more than 120 great features to Excel!

Download with all combine examples

All the examples above can be found in this example workbook.

Please click here and the download starts immediately.

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Image by anncapictures from Pixabay

Der Beitrag Combine Text in Excel: 5 Easy Methods to Concatenate Cells! erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/3oyMyVd

[professorexcel]

Bulk Insert Text: How to Add Text to Many Existing Excel Cells at Once

You have a couple (or many) cells with text in it. Now, you want to insert more text to them. Either at the beginning, in the middle or at the end. Here is how to easily do that!

Method 1: The fastest way to bulk insert text

Because it is the fastest and most convenient way, we go with this method first.

Use Professor Excel Tools to easily insert text – and select from further options (subscript, superscript or the position of where to insert the text).

Select all the cell in which you want to insert text.

Click on “Insert Text” on the Professor Excel ribbon.

Type your text and select further options (for example, you can specify the position (add the text in the beginning of the existing text, at the end or at a character position). Also, choose if you want o insert it as normal text, subscript or superscript.

Click on Insert.

Click here to learn more about Professor Excel Tools. Or click here to start the download.

This function is included in our Excel Add-In ‘Professor Excel Tools’

Learn more

Download Free Trial

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Method 2: Use a string formula to combine two text parts

The second method is based on formulas. You can combine two text strings with the & sign (actually, there are four different ways to concatenate text, but using the & sign is usually the fastest).

So, let’s see how it works:

Concatenate the two text parts with the & sign.

In this example, you have existing in cells B2 to B5. You want to add the word “Cool.” to it. So, the formula in cell C2 is:

=B2&" Cool."

Please note that I have added a space before the word cool (on purpose…). The reason is that between the previous full stops and the word cool should have a space.

You can now copy the new cell (range C2 to C5). Paste it using paste special on top of the existing cells as values if you want to fully replace the original text cells.

Method 3: Try a workaround to insert text with the Find & Replace function

Admittedly, this method is a little bit trial and error. If it works depends on your existing cells. The main idea is to replace text from the original cells with new text.

Let’s go back to our original example. You again want to add the word “Cool.” to your existing cells:

Insert text with the Find and Replace function.

In this case, we are lucky that all existing cells end with a full stop. We can use this to replace it the following way:

Select all original cells.

Press Ctrl + H on the keyboard so that the Find and Replace window opens.

As “Find what:”, enter “.”

Because we still want to keep the full stop, we also use this in the “Replace with:” field: “. Cool.”

Click on Replace All.

If the result is not as expected, you can simply undo the replace process (press Ctrl + Z on the keyboard).

Method 4: Bulk insert text with a VBA macro

If you feel comfortable to use a short VBA macro, you can copy and paste the following code into a new VBA module. Please refer to this article for help.

Replace the word ” Cool.” with your text to add at the end. Also, you can set a text to insert in the beginning. Then, place the cursor within these lines of code and press F5 on the keyboard.

Sub bulkInsertText() Dim textToInsertAtTheEnd As String, textToInsertAtTheBeginning As String 'Replace "Cool" with your text to insert at the end textToInsertAtTheEnd = " Cool." textToInsertAtTheBeginning = "" For Each cell In Selection If cell.HasFormula = False Then cell.Value = textToInsertAtTheBeginning & cell.Value & textToInsertAtTheEnd End If Next End Sub

Image by Gerd Altmann from Pixabay

Der Beitrag Bulk Insert Text: How to Add Text to Many Existing Excel Cells at Once erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/3kCXrnT

[professorexcel]

Delta ∆: How to Easily Insert Difference Sign “∆” in Excel

The capital Greek letter Delta (∆) stands for difference or increment in Mathematics. In some cases, it would also help to use it in Excel: Instead of writing “Difference” or “Change”, you could simply insert the Delta sign. Here is how to do that in Windows and Mac!

The fastest method for Windows: Professor Excel Tools

Insert the Delta sign ∆ with Professor Excel Tools.

This is the fastest method: Go to the Professor Excel ribbon, click on the drop-down arrow of “Insert Symbol” and then on the delta sign ∆.

Just download and install the Excel-add-in Professor Excel Tools and see if it works for you.

This function is included in our Excel Add-In ‘Professor Excel Tools’

Learn more

Download Free Trial

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Insert Delta Sign (∆) in Windows

Method 1 in Windows

The “traditional” way to insert the sign showing the difference in Windows is through the Insert Symbol window:

Insert the Delta Sign in Windows via the Insert Symbol window.

On the Insert ribbon, click “Symbol” (the right-most button).

In the Symbol window, select “Unicode (hex)” in the from field.

Type 0394 into the “Character code” field. You should now see the Greek character selected above.

Click on Insert to insert the symbol into the cell or chart.

Method 2 in Windows

Probably not as beautiful, but it usually works: Alternatively, just copy and paste it from here.

∆

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Insert Delta Sign (∆) in Mac

It’s almost the same in Mac OS as in Windows, but the window looks a bit different. Still, you have to go to the Insert ribbon and click on Symbol on the right-hand side:

Insert the delta symbol on a Mac with the Character Viewer (very similar to Windows).

On the Insert ribbon, click on Symbol (on the right-hand side).

In the Character Viewer, go to Mathematical Symbols.

Insert the ∆ by double-clicking on it.

Add the Greek Letter Delta to a chart

You want to show the delta sign ∆ on a chart? It works the same way as described above. The only difference: Insert a text field first.

Before you can insert the delta sign on a chart, insert a Text Box on it.

In order to do this, select your chart. Then go to the Insert ribbon. On the right-hand side, click on the button “Text Box” and draw it onto the chart. Now, you can simply copy & paste or insert ∆ there.

IImage by Pete Linforth from Pixabay

Der Beitrag Delta ∆: How to Easily Insert Difference Sign “∆” in Excel erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/3wLMIfu

[professorexcel]

Ʃ: How to Easily Insert Sum / Total Sign “Ʃ” in Excel

You have created a nice dashboard in Excel? Or a beautiful Profit and Loss calculation? Now, you want to bring it to the next level: Insert the sum or total sign, the Greek character sigma: Σ. In general mathematics, uppercase Σ is used as an operator for summation. Here is how to quickly do this in Excel!

The fastest method for Windows: Professor Excel Tools

Insert the SUM / Total sign Σ with Professor Excel Tools.

This is the fastest method: Go to the Professor Excel ribbon, click on the drop-down arrow of “Insert Symbol” and then on the total sign sigma Σ.

Just download and install the Excel-add-in Professor Excel Tools and see if it works for you.

This function is included in our Excel Add-In ‘Professor Excel Tools’

Learn more

Download Free Trial

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Insert Total Sign (Σ) in Windows

Method 1 in Windows

The “traditional” way to insert the operator of summation sigma in Windows is through the Insert Symbol window:

Insert the Total Sign in Windows via the Insert Symbol window.

On the Insert ribbon, click “Symbol” (the right-most button).

In the Symbol window, select “Unicode (hex)” in the from field.

Type 03A3 into the “Character code” field. You should now see the Greek character sigma selected above.

Click on Insert to insert the symbol into the cell or chart.

Method 2 in Windows

Probably not as beautiful, but it usually works: Alternatively, just copy and paste it from here.

Σ

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Insert Total Sign (Σ) in Mac

It’s almost the same in Mac OS as in Windows, but the window looks a bit different. Still, you have to go to the Insert ribbon and click on Symbol on the right-hand side:

On the Insert ribbon, click on Symbol (on the right-hand side).

In the Character Viewer, go to Mathematical Symbols.

Insert the Σ by double-clicking on it.

Add the Greek Letter Sigma to a chart

You want to show the operator of summation Σ on a chart? It works the same way as described above. The only difference: Insert a text field first.

Before you can insert the total sign on a chart, insert a Text Box on it.

In order to do this, select your chart. Then go to the Insert ribbon. On the right-hand side, click on the button “Text Box” and draw it onto the chart. Now, you can simply copy & paste or insert the total sign Σ there.

Image by Pexels from Pixabay

Der Beitrag Ʃ: How to Easily Insert Sum / Total Sign “Ʃ” in Excel erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/3kDfV7A

[professorexcel]

How to Center Across Selection in Excel With Just One Click

Especially for headlines or table headings in Excel, merging cells is a great and fast way to center text across several cells. But merging cells comes with a few disadvantages. So, many professionals recommend using “Center Across Selection” instead. Unfortunately, the option is a little bit hidden. Here is how to apply it and how to speed it up to use it with one click only.

Why to use Center Across Selection instead of merging cells

Merging cells has one major advantage: It’s very fast to apply and at the same time it usually looks good right away. Excel provides a distinct button on the Home ribbon, called Merge & Center. It even offers a few more options when clicking on the drop-down arrow.

Unfortunately, Merge and Center has a few disadvantages:

Depending on how you add Merge & Center, you might either lose data (when you merge cells from which more than one contains data) or you might hide data (if you apply the merging via the format painter).

It’s often more difficult to select single columns, especially when using keyboard shortcuts (Ctrl + Space).

In my experience, copying and pasting ranges with merged cells often takes more time or leads to strange looking results…

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

How to apply Center Across Selection

Excel provides an alternative to merging cells: It’s called “Center Across Selection”. Unfortunately, applying it takes a couple of steps:

Apply “Center Across Selection” via the Format Cells window.

Select the headline (or cells you like to center).

Open the Format Cells window: Press Ctrl + 1 on the keyboard.

Go to the Alignment tab. For “Horizontal”, select “Center Across Selection”.

Confirm with OK.

Do you want to apply it again? After formatting cells once like this, you can just press F4 on the keyboard. With this you repeat the last action – which in this case is to apply the formatting.

Speed it up: Apply it with one click only

Save a lot of time here: Just select the cells, go to the Professor Excel ribbon and click on “Center Across Selection”. Fast, right?

Apply “Center Across Selection” with just one click using Professor Excel Tools.

This function is part of the Excel productivity suite “Professor Excel Tools”. It comes with a new ribbon in Excel having more than 120 features to boost your productivity. You can try it for free: Just click this link and the download starts. Or click here to learn more.

This function is included in our Excel Add-In ‘Professor Excel Tools’

Learn more

Download Free Trial

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Image by StockSnap from Pixabay

Der Beitrag How to Center Across Selection in Excel With Just One Click erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/3FfHPyb

[professorexcel]

How to Trim Values Without Formula in Excel: Just One Click!

Your lookup (for example VLOOKUP) does not work? It cannot find results although it should? In such case, your values might have too many space characters: Leading space characters might still be visible, as well as double space characters in the middle of a text. But trailing spaces at the end of a text are very difficult to spot. In such case, trimming might help. Trim means in a nutshell that all unnecessary spaces are removed. Here is how to do that with five different ways.

Introduction: What does “trim” mean?

Trim means the following: You remove the following parameters from a text:

Leading space characters. So, if a text starts with a blank / space character.

Trailing space characters.

Replace double space characters by single space characters.

Here is an example (space characters symbolized by dots):

Example of trimming text.

Method 1: Trim with an Excel function

Excel provides a simple function for trimming. As you can guess – it’s called “=TRIM()”. It has just one argument: Your value to be trimmed.

Example for the TRIM function in Excel.

Advantage of this method: It’s dynamic so that it adapts when values change or are updated.

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Method 2: Trim values with just one click

The fastest method: Just select the cell and click on Trim on the Professor Excel ribbon. You can get the Professor Excel ribbon by installing Professor Excel Tools (click here for starting the download).

Trim cell with Professor Excel Tools.

This function is included in our Excel Add-In ‘Professor Excel Tools’

Learn more

Download Free Trial

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Method 3: Trimming in PowerQuery

If you use PowerQuery, you can also remove “unnecessary” space characters there. If not, just skip to the next method below.

In the PowerQuery editor, select the respective columns. Then, go to Transform, click on Format and then on Trim. That’s it, PowerQuery inserts a new step.

Insert TRIM with PowerQuery.

Method 4: Manual trimming

Trimming text values manually is not very easy. There is basically one simple thing you can do: Remove double spaces. Leading and trailing spaces cannot be removed with simple methods (you have to use one of the other methods introduced here).

So, how to remove double spaces? Use the “Find and Replace” window.

Use the Find and Replace window to replace double spaces with single spaces.

Open “Find and Replace” by pressing Ctrl + H on the keyboard.

For “Find what”, type double spaces ” ” (without the quotation marks)

For “Replace with”, type a single space character ” ” (also without the quotation marks).

Click on “Replace All”. Click on “Replace All” again until you receive a message “We couldn’t find anything to replace […]”.

Method 5: Use a VBA macro to trim values

You can also use VBA to trim text in Excel. The fastest way would be to open the VBA editor.

Select your cells in your Excel sheet,

copy and paste the following line of code into the immediate window and

press Enter on the keyboard.

For Each cell In Selection: cell.Value = Trim(cell.Value): Next

The immediate window is below the code area (highlighted in yellow below):

Please note: The trim function in VBA only removes leading and trailing spaces. Double spaces are not removed.

Image by Erubiel Flores from Pixabay

Der Beitrag How to Trim Values Without Formula in Excel: Just One Click! erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/3c78IrG

[professorexcel]

Clear All: How to Delete Complete Excel Cell – Including Formatting!

When you press Del on the keyboard in Excel, the cell contents will be removed. However, often you also want to delete the formatting. That is called “Clear All” in Excel. Here is where to find the function and how to speed it up with keyboard shortcuts.

Use the Home ribbon to clear all

Of course, Excel provides a solution for the problem to delete all – including formatting. Unfortunately, it’s a bit hidden:

Go to the Home ribbon, click on Clear and then on Clear All.

Select the cells to clear and go to the Home ribbon.

On the right-hand side of the Home ribbon, click on Clear.

The dropdown opens. Click on Clear All.

That’s it.

Instead of clearing all, you also have the following options:

Removing just the formats. Your default format (usually Font Calibri) will be applied.

Delete the contents. The result is the same as pressing Del on the keyboard (or Fn + Back on a Mac).

Delete the comments of all selected cells.

Remove the hyperlinks if there are any.

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Recommendation: Add “delete everything” button to Quick Access Toolbar

Just a small advice here: It might be worth adding this button to your Quick Access Toolbar. Instead of clicking on “Clear All” with the left mouse button, right-click on it. Then, click on “Add to Quick Access Toolbar”.

For more information about the Quick Access Toolbar, please refer to this article.

Clear all with a keyboard shortcut

The first keyboard shortcut is based on the Alt key. So, you press the following buttons after each other:

Alt --> H --> E --> A

You want to do that much more comfortably? Our Excel add-in Professor Excel Tools provides a much easier keyboard shortcut:

Ctrl + Del

Professor Excel Tools comes with more than 120 features to boost your productivity and improve your results. You can download it here and try it for free.

This function is included in our Excel Add-In ‘Professor Excel Tools’

Learn more

Download Free Trial

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Image by Pexels from Pixabay

Der Beitrag Clear All: How to Delete Complete Excel Cell – Including Formatting! erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/3qA8t0L

[professorexcel]

Fun in Excel: 6 Ways of Fun with Spreadsheets

Let’s have some fun in Excel! You probably do your work with spreadsheets, but there are also ways of having fun in Excel. In this article, we introduce you to 6 fun applications for Microsoft Excel.

1: Play Tetris in Excel

How to setup Tetris in Excel

Probably one of the most addictive games: Tetris is a real classic among all computer games. There are countless versions, but have you ever played it in Excel? You can download the Excel file here.

The setup is quite easy, just run the setup macro:

Click on Macros on the Developer ribbon.

Select “setup”.

Click run.

Ever played Tetris in Excel

Now you can just start playing by pressing Ctrl + t on the keyboard. The other keys are shown on the right hand side.

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

2. Solve your Sudokus in Excel

Play or solve Sudokus in Excel

Another popular game is Sudoku. You have to solve this 9x9x9 number quest in which each 9×9 field can only contain every number from 1-9 once. Also every row and column must have each number from 1 to 9 exactly one time.

There is a version for Excel available. You can download it directly with this link. You can also visit the website of the developer. This Excel version of Sudoku doesn’t only create new Sudokus, but it can also solve existing ones. Please note that it is not possible to play it on a Mac.

3. Create your picture in Excel cells

Your picture in Excel: Each cell has one color. If you zoom out, it looks like this.

Admittedly, this sounds a little bit “nerdy”: You can create a picture in Excel only with conditional formatting. Every pixel is represented by the three colors red, green and blue.

If you want to create your image with Excel cells, you can upload your picture to this website. After some seconds, you’ll get the download of an Excel file. When you zoom out – for example to 10% – you can see your picture as shown on the right hand side.

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

4. More fun? Play a slide puzzle

The slide puzzle.

Another small game in Excel: The slide puzzle. The goal is to sort all the tiles until the image is complete. You can easily start by clicking on ‘Shuffle Tiles’ and change the position of the tiles per drag and drop. The download is available here.

5. One more game: CellSweeper

Game in Excel: CellSweeper.

It works a little bit like Minesweeper, which you probably know from your old Windows computer. Instead of just clicking around, you walk trough the mine field and try not to step on a bomb or meet a zombi (instruction are available).

You can download CellSweeper here. Please note that CellSweeper only works on Excel for Windows.

Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Learn more

Download Free Trial

Add more than 120 great features to Excel!

6. (New) Follow up the most famous chess games

See the most famous chess games step by step.

Are you – at least a little bit – interested in chess? Even if not, this is an impressive piece of VBA work in Excel. Follow up the most famous chess games step by step. Select your game on sheet “Board” in cell B4, type 0 in cell K6 (in order to start at the beginning) and use the spinner to see the game.

You can download the file from the Microsoft Techcommunity here.

Do you know any other fun application for Excel? Please leave a comment below and let us know!

(adsbygoogle = window.adsbygoogle || []).push({}); (function() { var done = false; var script = document.createElement('script'); script.async = true; script.type = 'text/javascript'; script.src = '//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js'; var createScript = setTimeout( function(){ document.getElementsByTagName('HEAD').item(0).appendChild(script); }, 5000 ); script.onreadystatechange = script.onload = function(e) { if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) { (adsbygoogle = window.adsbygoogle || []).push({}); } }; })();

Der Beitrag Fun in Excel: 6 Ways of Fun with Spreadsheets erschien zuerst auf Professor Excel.

from Professor Excel https://ift.tt/3C3xmE9