How To Create A Navigation Menu Pane In Excel - No VBA

[@alinabankal3018]

This is exactly what I was looking for. Thank you so much for creating this. Your steps were so easy to follow I'm well on my way to making a menu driven dashboard! Thank YOU!!

[@Pinexl]

Glad it helped!

[@syedashfaq7245]

Same here

[@lynasoua9209]

Just because you used Game of Thrones character as team member names, you got my like!!!

[@84rinne_moo]

A way to alter this is to create the dashboard on one tab, and then place a link in every other tab that links back to the dashboard tab. This might help some ppl with the kind of sheets/data you may have. I also would place this just above my headers and then freeze the two top rows to make it able to scroll through my data easily and filter easily with no problems whilst keeping the link back to the main dashboard handy at all times .

[@recipehacker9752]

Thanks ! Please share how you’d freeze those rows, as you had suggested (I.e., to keep them at the top of the window even during scrolling). I was not aware freezing rows was possible . Thank you.

[@noblestride]

THANKS A BILLION!!! MADE MY SHEET CLUTTER FREE AND AMAZING!

[@Pinexl]

Happy you found it so useful!

[@suhaimiabdazais]

Just found and subscribed .. This is what I'm looking for.. Tqvm

[@Pinexl]

Thanks! Happy you find the content useful!

[@shaniwafa]

Awesome 👍👍👍 Very simple Technic Result is fantastic!! Thanks for idea Expecting more tricks and animation Technic in future

[@Pinexl]

More to come! Thank you for your comment!

[@manjunathk2767]

Simple but very rich. Thanks

[@Pinexl]

Thank you very much!

[@Mamounhefni]

This smart, simple and elegant !!

[@Pinexl]

Thank you very much!

[@luisenc789]

You're a genius man! I love you!

[@Pinexl]

Thanks a lot! We love you too!

[@diassetefle9775]

Thanks for this Amazing tutorial, it looks like a web page

[@Pinexl]

Glad it was helpful!

[@anthonibritto5409]

Your videos are so amazing and has inspired a lot. Please keep up the spirit.

[@Pinexl]

Thank you very much! Really appreciate it!

[@Jennk-lb5no]

Nice! Thanks for sharing! can't wait to try this for my next report

[@Pinexl]

Happy to help! It should look amazing!

[@NixelPresentation]

Yes. I could learn something new.

[@Pinexl]

Hope it helps!

[@alonsozuniga101]

Thanks a lot mate, excellent explanation!

[@Pinexl]

Glad you liked it!

[@DarA-by4fl]

Thank you for this tutorial. This is really impressive!

[@Pinexl]

Thank you for watching!

[@hamedsougue9818]

Nice lesson thank so much

[@Pinexl]

Glad you liked it!

[@helendemore2225]

Excellent presentation 🙂

[@ganeshshetepatil9829]

Thanks for such nice demonstration...

[@Pinexl]

And thank you very much for your comment!

[@darkoldrocknroller]

WOW, very impressive. Thank you.

[@Pinexl]

Thank you very much! We're happy you like it!

[@belalzaqaibeh5072]

Perfect

[@arijturner8419]

WOW I NEED TO DO IT NOW

[@allen6031]

This video made me to subscribe.... 👍

[@Pinexl]

Thank you subscribing! More videos are coming soon!

[@user-ou2wu7iy7k]

Then how to take Print out, because when i tried, the print is coming with Dashboard, kindly advice

[@Pinexl]

Hello! You can hide the navigation panel by hiding the columns it is in. This way you can print the spreadsheet without it.

[@AbuTalha-eo7pr]

Very Helpful. Thank you

[@Pinexl]

Thank you for watching!

[@MsBoolo]

Very impressive 👏 👌 👍

[@Pinexl]

Thank you! Cheers!

[@mnsoyan]

Amazing idea!!

[@Pinexl]

Thank you very much!

[@myrtle411]

This is a huge help, however, is there a way to prevent the menu pane from being distorted page-to-page because of row height changes in the datasets (e.g. lots of wrapped text creating a larger row height)?

[@Pinexl]

Good question! Unfortunately, except for locking the sheet, so that the height of the rows is not changed, there really isn't a way using this method. As an alternative, you could use containers, objects or text boxes with hyperlinks, which you can adjust, so that they are not sized with cells.

[@mansiagarwal3715]

Hi, The tutorial is simple and easy to understand. I wanted to ask that how to hide the sub menus of "Data Input" from the other two menus.

[@Pinexl]

Hello and thanks for the comment! The submenus are only shown in the Data Input sheet. If you don't add them in the others, they won't be visible!

[@pv0315]

Beautiful work

[@clarissaconvertino4742]

Hi, thank you for the nice tip. In my case, each tab contains table, whose rows I want to filter eventually. Unfortunately the filtering function will hide certain rows, messing up the navigation pane. Any idea on how to fix this?

[@Pinexl]

Good question! Unfortunately, this is the disadvantage of using this method. You have two alternatives: 1. Build the navigation menu horizontal in the first rows, not columns. This way you could filter data without breaking the navigation menu. However, if you hide columns, you'd have similar issues as in the beginning. 2. Instead of using cell values to write the sheet names, you can insert buttons - some simple square shape for example, and add a link in it to the other sheets. Hope this helps!

[@dorzhimunkuev3959]

Hello there, I just inserted a text box and attached the link. Then I changed the parameter of object positioning: move and size with cells to don't move or size with cells. Afterward while filtering it does not disappear or move. Generally, it looks the same as shown in this tutorial and you can apply some additional effects in shape tool. I hope it helps you.

[@shimonkephaonwuneme3326]

@@dorzhimunkuev3959 sure I did same using text box for everything. Then modify textbox size and properties to not move with the cells

[@yobrunogabriel]

Very creative! Thanks

[@Blue4736.]

That was beautiful

[@Pinexl]

Thank you!

[@ehsansepahsalari617]

So helpful, Thank you so much

[@Pinexl]

Glad it was helpful!

[@mydigitalquill8945]

very well done!!

[@Pinexl]

Thanks a lot!

[@dorsoolms4684]

Does this work on excel for ios? Cause it looks like it doesn't (at least not with shape buttons) and vba doesn't work either there. I would like to have buttons on my excel sheets on the ipad but so far no luck. They behave differently than on windows and when protecting a sheet they don't work/react at all.

[@inglesfuncional]

Is there a way to freeze the navigation menu, so it would stay visible when we scroll vertically? Thanks

[@Pinexl]

Hi and thanks for the question! You could build the navigation menu in the top rows horizontally, instead of vertically, and then freeze the rows. This way it will stay in place when scrolling down.

[@millet2963]

How to lock your template and only use the slicer and the navigation panel?

[@Pinexl]

Hi and thanks for the comment! You can explore the Protect Sheet option in the Review tab. You can for example lock everything apart from selecting a cell.

[@mazhariqbal3654]

Its Ok, but problem is that when we scroll down the sheet, navigation cells navigate too, any solution?

[@Pinexl]

Hi and thank you for your comment! You could make the menu horizontal, not vertical and freeze the top rows. This will make sure the menu is always visible when scrolling down.

[@Hermetic_]

Nice thanks

[@futuresuperstar4life]

I NEED HELP! PLZZ im trying to do something similar but more specific i guess. im a server and ive been working on an excel file to track my tips and sales percentages tip outs etc, it's very complex and im completely new to excel so it took me like two months. i'm trying to recreate an app that i was using but doesn't track enough details. in this app there are tabs on top for sorting the data yearly, monthly, weekly and biweekly and "all" and then at the bottom you can toggle between all of the data with arrows, between the 2 arrows there are 2 dates based off of the filter (the corresponding dates of the month, the week etc) but youre not limited to just "this month" and "last month", but you can continue to toggle between all months by clicking the arrow. i've tried with slicers and it kind of works but not as efficient or specific as i'd like it to be PLEASE TELL ME if you think that is at all possible on excel

[@reinforcedmacky9270]

Thanks

[@arulpriyacreations]

How to give different datas based on the dates in one row in data entry form

[@kuldeepsinghbhati1156]

Why you hide column A

[@Pinexl]

Hello and thanks for asking! When you go to a new worksheet by clicking a hyperlink, cell A1 is automatically selected. By hiding column A, this way it appears that no cells are selected when you jump to the new worksheet. Of course, this is totally up to you if you want to implement or not.

[@kuldeepsinghbhati1156]

@@Pinexl Nice trick bro 👍 i want your help.. I am working on a dashboard of employees monthly salary... I have one sheet contain employees name and gross salary. Other 12 sheets for monthly attendance. I want that i just select employee name and month in dashboard and it gives me that month earned salary according to total working days on that month. i did this for one month salary. I changed employee name from drop down and i get selected month salary but this happen only for month. How i link employees name to different months..... Please give some idea

[@Pinexl]

@@kuldeepsinghbhati1156 You can use an index-match formula based on two conditions, in your case on employee name and month. We have used such formula in our Dynamic Controls tutorial. A free download with examples is also available. You can find the tutorial here: www.pinexl.com/excel-dashboard-controls-tutorial

[@kuldeepsinghbhati1156]

@@Pinexl thanks bro... You are so helpful... God bless you

[@Pinexl]

@@kuldeepsinghbhati1156 You're welcome!

[@eminimamoglu3072]

Thanks a lot!

[@Pinexl]

Thank you very much! Glad you are finding it useful!

[@AD-qb2kr]

I have also this annoying white flash between the sheets are loaded, maybe its because of tons of hyperlinks. Any solution?

[@Pinexl]

Maybe this depends on the Excel version you're using. If you try it in a new blank document, this will show if it is because of the version of something else.

[@TheLionOfJudahTheLambOfYHWH]

I appreciate your knowledge and effort to teach--- but for the rest of us who have never experienced this, please slow down, bud--way too fast and you did not explain what you did here 2:06-2:21 this section is not seen anymore--where did it go ?

[@Pinexl]

Hello and thank you for your comment! We'll keep the pace in mind for future videos. Regarding your question - the selected cells where simply merged, in order to have enough space for the title.

[@kadirbasar1547]

Thank you

[@Pinexl]

You're welcome!

[@hugyourdata1549]

Thank you so much!!!

[@Pinexl]

Glad it helped!

[@emtyas]

Just amazing.

[@Pinexl]

Thank you very much!

[@chanchaldas6229]

Nice bro

[@Pinexl]

Happy you like it!

[@victormerino4439]

Thanks bro

[@Pinexl]

Happy to help!

[@kkm5384]

when we copy the sheets 1:39 then subsequently, whatever changes made in first sheet same is being appear in the all sheets. we need to remove the link of the cell copy. how to do that?

[@Pinexl]

Hello, it seems like you've selected more than one sheets at the same time. This is why the changes are applied to all of them! Make sure a single sheet is selected after copy/pasting them.

[@kkm5384]

@@Pinexl Thank you. i think its working now. but i dont remember how did. i think i selected first sheet and copied same in all sheets. But what are saying is not not clear to me by saying "...you have selected more than one sheet...". Anyway, that part is OK. i am trying to use this excel book as a template for my write-ups for drafting. Is there a word limit option for the merged cell? i mean i want to select say 20x30 cells and merge them like a text box. then use it like a text box, with word limit. that helps me.

[@rajnishbedi7470]

Very Good

[@Pinexl]

Thank you!

[@OrdinaryCritic]

I’m just wondering. What if I scrolled down? Won’t the pane also scroll down? Thanks in advance. :)

[@Pinexl]

Hi, the pane will stay there, but only the colored part. The menu links won't scroll down. Depending on your needs, you can create the navigation in a similar way at the top rows, instead of having it vertically on the left. This way it will be hidden when you scroll down, unless you freeze the top rows.

[@OrdinaryCritic]

@@Pinexl, thanks. I’ve managed to use only five main “buttons” that “collapses and expands.” I froze them so they’d stay. It may just be an illusion, but it looks great. This idea of yours is was an eye-opening game changer.

[@Pinexl]

Thanks, I'm happy you you're finding this useful!

[@tomite2001]

How do you call your forms or dashboards from those menu items?

[@eclippse]

Hi and thanks for the question! The menu items are actually text cells, which have a hyperlink leading to different sheets in the workbook.

[@ITCE2018]

Nice

[@Pinexl]

Thank You!

[@julie92114]

Do you have this written out step by step ?

[@Pinexl]

Hello and thanks for the comment! At the moment this information is available only on KZbin.

[@panagiotistheodorou4959]

Very Interesting but way too fast for learning from it... That is for slow people like myself....

[@Pinexl]

Thank you for your comment and suggestion. We'll keep it in mind!

[@harriscastro535]

What if the excel tabs are not hidden in another pc. Would it still hide the tabs?

[@eclippse]

Hi Mark, thanks for asking! This is a workbook-level setting, so yes - the sheets will stay hidden on all PCs.

[@allabout1135]

I think is time for Pwer BI ;)

[@Pinexl]

Power BI is indeed an amazing tool! However, there are sill many organizations, where Excel is still the main data analysis, reporting and visualizations tool. This is why we want to help people make the best of it!

[@harshitgupta8844]

Sir I already made my project and now I have to do this thing... Please help me sir

[@Pinexl]

Hi and thank you for your comment! Check out our brand new Excel add-in Pine BI, which allows you to create the navigation menu from the video in seconds: www.pinexl.com/pine-bi-excel-add-in

[@IwanRudiawan]

to fast

[@Truth_Make_You_Free]

Way too fast. Not good. 😏